Re: VBA to connect to postgresql from MS Access

[Bret Stern]

Not sure what version of access you're using or how you are calling the
connection..ADO, DAO, .NET

I don't hard code connection strings anymore, but I did when I first
started.
Nowadays I call a registry function to put the drivers on a specific pc
into a listbox
and have a setup module which then builds the connection string on the
fly from the
select listbox value.

The below sample may not work, I haven't tested it, but the basic
elements are there to help
you start thinking about how it does work.

The DRIVER= element of the connection must have the name of the driver
installed on your
pc. Usually they can be found under the ODBC administrator tool, found
in Control Panel, Administrative Tools,
ODBC Data Source Administrator. If you're creating a DSN-LESS
connection, then click the Drivers tab and scroll to the
Postgresql drivers

The DRIVER= element must exactly match the name of the driver in the
[Drivers] tab under the ODBC administrator
tool. In the code example below, the driver name on my pc is PostgreSQL
ANSI

The other elements are well documented
Server= (an ip address or hostname of the database server you are
connecting to)
Port= (Can generally be left at 5432...the postgresql default port)
Database= (is the case sensitive name of the postgresql database you are
connecting)
UID= (postgresql user name to make the connection under)
PWD= (the password the connecting user)

If you are still having problems, there is the pg_hba.conf file which
may block access.
You can read about that here.
https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html


try creating a simple form, add a button, and have the button click
event call the below
function AFTER you have modified the elements to suit your database
name, server etc

hopefully some of this info will help make sense


Function pg_db_open() as boolean
   Dim conNew As New ADODB.Connection
   Dim cs as string
   cs = "DRIVER=PostgreSQL ANSI; Server=your-server-ip; Port=5432;
Database=your-database-name; UID=username; PWD=password"

On Error GoTo conError

conNew.ConnectionString = cs
conNew.Open
Set conNew = Nothing

pg_db_open = True
Exit Function

conError:
Set conNew = Nothing
pg_db_open = False
End Function




On Fri, 2018-06-01 at 14:27 +0200, Arnaud L. wrote:

> Le 01-06-18 à 14:21, Łukasz Jarych a écrit :
> > ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*
> > 
> > When i was searching code for this in internet i found only:
> > DRIVER={PostgreSQL 
> > Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"
> > 
> > but this is not working, why? I do not know what these CA, BO, BI 
> > strange paramaters.
> Why is this not working, we cannot answer without the error message you 
> get. You probably use the wrong driver name.
> 
> As for the CA, BO, etc... they are abreviations of the keywords. You can 
> use either the keywords or the abreviations in your connection string, 
> and they are all explained here :
> https://odbc.postgresql.org/docs/config-opt.html
> 
> 


-- 
Bret Stern
Machine Management
Industrial and Commercial IT Services

707-775-9792 (cell-text-direct)

Like Query help

[Bret Stern]

query

select company_code, item_code, item_description, product_line, 
udf_item_width, udf_item_length, sales_unit_measure, ''as mat_type from 
mas_combined_item_master where company_code='BUR' or company_code='SNJ' 
or company_code='EBC' and udf_edb_managed=''

and item_code LIKE 'S-%' order by item_code;

comment

Second column is item_code...why are these items in the results. 
Expecting the query to return results where the item_code


starts with "S-" and includes any othervalue past "S-"


results

"SNJ";"01 1X1 CORNER BULLNOSE";"1x1 Corner Double 
Bullnose";"SYZY";"";"";"EACH";""
"SNJ";"0001 BRIGHT WHITE LISTELLO";"Bright White Listello 
5/8x12";"VILL";"";"";"EACH";""
"SNJ";"0001 CUSTOM 1X6 BULLNOSE LONG";"Custom 1x6 Bullnose on 6" 
Side";"SYZY";"";"";"EACH";""

"BUR";"000936MOD1P4";"Matte Cappuccino Tile";"AMOL";"";"";"SF";""
"SNJ";"000936MOD1P4";"Matte Cappuccino Tile";"AMOL";"";"";"SF";""
"BUR";"0009S4369MOD1P2";"Matte Cappuccino BN on 6"";"AMOL";"";"";"EA";""
"SNJ";"0009S4369MOD1P2";"Matte Cappuccino BN on 6"";"AMOL";"";"";"EA";""
"BUR";"0009S4639MOD1P2";"Matte Cappucino BN on 3"";"AMOL";"";"";"EA";""
"SNJ";"0009S4639MOD1P2";"Matte Cappucino BN on 3"";"AMOL";"";"";"EA";""
"BUR";"0009SCL4369M1P2";"Matte Cappuccino Left 
Bullnose";"AMOL";"";"";"EA";""
"SNJ";"0009SCL4369M1P2";"Matte Cappuccino Left 
Bullnose";"AMOL";"";"";"EA";""

"BUR";"0009SCR4369M1P2";"Matte Cappuccino Right BN";"AMOL";"";"";"EA";""
"SNJ";"0009SCR4369M1P2";"Matte Cappuccino Right BN";"AMOL";"";"";"EA";""
"SNJ";"0014 SURF 2X4 OFFSET";"Surf 2x4 Offset Mosaic";"VILL";"";"";"EACH";""
"SNJ";"0014 SURF LISTELLO";"Surf Listello 5/8x12";"VILL";"";"";"EACH";""
"SNJ";"0015 ICE 2X4 OFFSET";"Ice 2x4 Offset Mosaic";"VILL";"";"";"EACH";""

Re: Like Query help

[Bret Stern]

Works. Thanks for the help. I will read the docs

Best

On 1/14/2021 4:37 PM, aNullValue (Drew Stemen) wrote:

At 2021-01-14T19:27:23-05:00, Bret Stern  
sent:

query

select company_code, item_code, item_description, product_line,
udf_item_width, udf_item_length, sales_unit_measure, ''as mat_type from
mas_combined_item_master where company_code='BUR' or company_code='SNJ'
or company_code='EBC' and udf_edb_managed=''
and item_code LIKE 'S-%' order by item_code;

comment

Second column is item_code...why are these items in the results.
Expecting the query to return results where the item_code

starts with "S-" and includes any othervalue past "S-"


Based on your description, your query is not correct; you should rewrite it, 
likely using parenthesis to define your actually desired value expression.

See also 
https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-EXPRESS-EVAL

This may or may not be what you intended:

SELECT company_code, item_code, item_description, product_line, udf_item_width, 
udf_item_length, sales_unit_measure, ''as mat_type
FROM mas_combined_item_master
WHERE (company_code='BUR' OR company_code='SNJ' OR company_code='EBC')
AND udf_edb_managed=''
AND item_code LIKE 'S-%'
ORDER BY item_code;

Inserts create new records in reporting table

[Bret Stern]

Love the forum,

I'm bringing 100k - 200k of AR transactions into a table from several 
separate ERP companies.


As I insert the records, does it make sense to run a trigger event to 
build or insert data into


a reporting table (to save generating a query to build the reporting 
table later.)



For example, I bring in 120k records which contain invoice dates from 
the beginning of time


in the ERP system. (eg; records with dated transactions from 2010...2022)


Conceptual data

[Location][InvoiceDate][Amount]

Dallas        2012-01-09    7500.00
Dallas        2012-11-19    1200.00
Dallas        2012-04-02    500.00
Phoenix       2012-01-03    1200.00
Phoenix        2012-04-12    7500.00

Would it be possible, or would it make sense to create a trigger which 
populated another


table like below after each insert...or is is a resource killer..or just 
unnecessary, and smarter to create a query


that performs calculated on the fly columns for the dates of invoice?


[Location][InvoiceDate][Amount]    [Jan]            [Feb]     [Mar]     
       [Apr]      [May]         [Jun]  [Jul]         [Aug] 
          [Sep]            [Oct]     [Nov]         [Dec]


Dallas 2012-01-09   7500.00

Dallas 2012-11-19 1200.00

Dallas 2012-04-02 500.00

Phoenix    2012-01-03    1200.00

Phoenix 2012-04-12 7500.00


Bret

Format an Update with calculation

[Bret Stern]

My statement below updates the pricing no problem, but I want it to be
formatted with 2 dec points eg (43.23).

Started playing with to_numeric but can't figure it out. Lots of
examples with to_char in the
manual, but still searching for answer.

Can it be done?

I want suggested_retail_price to be formatted to 2 decimal points

UPDATE im_ci_item_transfer
   SET suggested_retail_price=(suggested_retail_price +
(suggested_retail_price * .13))
WHERE item_code='0025881P2';

Feeling lazy, sorry guys

Re: Format an Update with calculation

[Bret Stern]

Thanks again,
I don't remember ever using a select in an update.
Not sure how to use a select in an update, I'll google around.


On Tue, 2018-12-18 at 08:18 +0100, Pavel Stehule wrote:
> 
> 
> 
> út 18. 12. 2018 v 8:15 odesílatel Bret Stern
>  napsal:
> 
> My statement below updates the pricing no problem, but I want
> it to be
> formatted with 2 dec points eg (43.23).
> 
> Started playing with to_numeric but can't figure it out. Lots
> of examples with to_char in the
> manual, but still searching for answer.
> 
> Can it be done?
> 
> 
> 
> postgres=# select random();
> ┌───┐
> │  random   │
> ╞═══╡
> │ 0.261391982901841 │
> └───┘
> (1 row)
> 
> postgres=# select random()::numeric(7,2);
> ┌┐
> │ random │
> ╞╡
> │   0.67 │
> └┘
> (1 row)
> 
> 
>  
> Regards
> 
> 
> Pavel
> 
> 
> 
> I want suggested_retail_price to be formatted to 2 decimal
> points
> 
> UPDATE im_ci_item_transfer
>SET suggested_retail_price=(suggested_retail_price +
> (suggested_retail_price * .13))
> WHERE item_code='0025881P2';
> 
> Feeling lazy, sorry guys

Sort question - Fractions, Metric etc

[Bret Stern]

I have a table with metric, imperial, fraction columns.

Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 
1., 1.125)


Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column 
and sorted based


on those values eg; 1,2,3,4,5,6 indicating the value I need to show in 
which sequence.



Just curious what the pro's do

Bret

Re: Sort question - Fractions, Metric etc

[Bret Stern]

Strings;

I haven't explored doing this with numeric types, but some columns 
needed alpha chars eg 13mm.


Although I could have front ended this UI with mm nomenclature I did not.

I'll put a table together with appropriate numeric types and see if the 
sort will behave.




On 8/14/2021 9:21 AM, Adrian Klaver wrote:

On 8/14/21 9:14 AM, Bret Stern wrote:

I have a table with metric, imperial, fraction columns.

Is there a way to sort correctly using imperial (eg; .125, .375, .437 
-> 1., 1.125)


Alright how is this different from metric or fraction?

I can sort of see fraction if you mean as 1/3, 1/20, etc.

What is the data type of the field you are storing this in?



Couldn't handle it with ORDER BY ASC, DESC args so I added a 
sort_column and sorted based


on those values eg; 1,2,3,4,5,6 indicating the value I need to show 
in which sequence.



Just curious what the pro's do

Bret

Re: Sort question - Fractions, Metric etc

[Bret Stern]

I like that idea

On 8/14/2021 9:46 AM, Adrian Klaver wrote:

On 8/14/21 9:37 AM, Bret Stern wrote:

Strings;

I haven't explored doing this with numeric types, but some columns 
needed alpha chars eg 13mm.


Two columns:

data_val(numeric)    data_unit(varchar)
13    mm



Although I could have front ended this UI with mm nomenclature I did 
not.


I'll put a table together with appropriate numeric types and see if 
the sort will behave.


It will.





On 8/14/2021 9:21 AM, Adrian Klaver wrote:

On 8/14/21 9:14 AM, Bret Stern wrote:

I have a table with metric, imperial, fraction columns.

Is there a way to sort correctly using imperial (eg; .125, .375, 
.437 -> 1., 1.125)


Alright how is this different from metric or fraction?

I can sort of see fraction if you mean as 1/3, 1/20, etc.

What is the data type of the field you are storing this in?



Couldn't handle it with ORDER BY ASC, DESC args so I added a 
sort_column and sorted based


on those values eg; 1,2,3,4,5,6 indicating the value I need to show 
in which sequence.



Just curious what the pro's do

Bret

Re: Sort question - Fractions, Metric etc

[Bret Stern]

Yep,

I provide the UI for the user to select the vendor supplied unit value 
from the list.


I don't want anyone entering (manually) any value, they must select from 
my list (fed from a PG table).


(and since some units don't translate exactly, they pick the one that is 
closest).


It's Tile (floor, shower etc), not the tightest tolerance of dimensions, 
so it's not catastrophic if it's off


a 32nd or so.

cheers





On 8/14/2021 1:43 PM, Adrian Klaver wrote:

On 8/14/21 1:24 PM, Bret Stern wrote:
Here's the clip of the UI. The user selects whatever value the vendor 
provides for unit thickness. The data entry


So the vendors supply the measurements in all the various units for a 
given item?




people aren't comfortable converting.

At this point the sort_order column managed to do the trickbut 
going forward on my next


application, I can see a different approach.

Are you suggesting a function like this shellsort used with an array.

Re: Sort question - Fractions, Metric etc

[Bret Stern]

On 8/14/2021 2:13 PM, Adrian Klaver wrote:

On 8/14/21 2:04 PM, Bret Stern wrote:

Yep,

I provide the UI for the user to select the vendor supplied unit 
value from the list.


I should have been more specific, does the vendor do all the 
conversions and supply them to you?


Vendors supply dimensions, depending on where in the world the product 
comes from, could be metric, imperial


or fraction




I don't want anyone entering (manually) any value, they must select 
from my list (fed from a PG table).


(and since some units don't translate exactly, they pick the one that 
is closest).


Yes, nominal dimensions. That is what I'm trying to work out, is this 
something that makes sense to the vendor and should not be tampered with?


The vendor is out of the picture at this point. They provide a catalog, 
and we enter (or import) items into our POS system. We match their 
dimension within tolerance.


It's Tile (floor, shower etc), not the tightest tolerance of 
dimensions, so it's not catastrophic if it's off


Is it only tile you are working with?


No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks 
and delivered in blocks of ten slabs normally, where tolerances are 
also  +/- .032


I will say this business has been behind in the attributes game. Plus 
there are many "artsy" vendors


who can hardly speak in these terms, and don't publish to us, so we do 
the best we can.


Getting vendors to supply the basic values is a struggle.





a 32nd or so.

cheers

Re: The tragedy of SQL

[Bret Stern]

I didn't start in 1967, but 1984, I'm in agreement with the bad 
programmers premise. Since the beginning there have always been


lots of languages. It is my opinion, the more languages and concepts you 
know the better your success on the project.


Heck I didn't use triggers till late 90's, funny thing I have a PICK 
project right now.. too much fun




On 9/14/2021 9:10 AM, Michael Nolan wrote:
I started programming in 1967, and over the last 50+ years I've 
programmed in more languages than I would want to list.  I spent a 
decade writing in FORTRAN on a GA 18/30 (essentially a clone of the 
IBM 1130) with limited memory space, so you had to write EFFICIENT 
code, something that is a bit of a lost art these days.  I also spent 
a decade writing in COBOL.


I've not found many tasks that I couldn't find a way to write in 
whatever language I had available to write it in. There may be bad (or 
at least inefficient) languages, but there are lots of bad programmers.

--
Mike Nolan
htf...@gmail.com