Weirdness with ODBC connection to 2 PostgreSQL dbs

[ML!]

I have a connection string that references all of the exact same parameters except server, database name, UID and PWD for 2 databases.

The connection to one works perfectly, the other fails in the exact same function. I get a 3151 error

Err.Description is "ODBC--connection to '{PostgreSQL Unicode}IP_address' failed where IP_address is the actual server.

tdf.Connect = "ODBC;Driver={PostgreSQL Unicode};Server=IP_address;Port=5432;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;"

I'm trying to use dsn-less connections to make it easier for users in multiple locations to connect.

I connected to the tables via a system data source for testing purposes and I can gleefully open them from both databases and read and write to them.

The oddity is that when I try to use a string variable for the connection string, when I debug.print tdf.Connect on either of them, the one that fails doesn't have the driver parameter in it any longer so I'm using the entire string for tdf.Connect.

Thoughts? Ideas? TIA!

[PhilS]

The oddity is that when I try to use a string variable for the connection string, when I debug.print tdf.Connect on either of them, the one that fails doesn't have the driver parameter in it any longer so I'm using the entire string for tdf.Connect.

I do not get the last part using the entire string for tdf.Connect. - Why would you not use the entire string?

You did a debug.print of tdf.Connect. Did you do a debug.print of your connections string before assigning it to tdf.Connect? Maybe the driver name was missing right from the start?
Or do you actually use the hardcoded assignment as shown in the code of your posting?

[ML!]

Thanks for your response Phil

I did a debug.print of the tdf.Connect and of the strConnect variable defined by the value in tdf.Connect but it drops the driver so I stopped using the variable and used the whole string for tdf.Connect

I was wanting to use the variable so I could loop through the tables of each db and copy them to local tables, the string would vary depending on which db the linked table belonged in

That missing driver was the only difference between the successful connect on the one database and the failure on the other

Either way the 3151 error is still thrown on the one database. Using the exact same code on the other, (with different connection string parameters) it failse. I have some other code in another procedure which succesfully uses an ADODB connection but was trying to avoid 2 different connection mechanisms and stick to DAO.

[ML!]

Oh...also Phil, I can't get the dns-less connection working with ADO either which is the ultimate goal.