How to add a connection string to VBA DoCmd

[CptCrusty1]

I have some VBA that runs a query via a Docmd statement. Problem is, everytime I close the db, the connection is lost. I'd like to add the connection string including the username and pw somehow so that every time the query runs, it relinks, everytime. I've already generated my connection string, I just need to add it somehow.

Thanks
Crusty.

I live to work and I work to live.

 

[abaldwin]

Can you post your code so far?

Thanks


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[JPMontreal]

I it is a “Pass Throught“ query, you add it to the query’s properties in design view.

Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[CptCrusty1]

I'm not using a passthrough. Here is a sample of my code... very simple VBA....

[blue]
DoCmd.TransferSpreadsheet acExport, 8, "qrySEL_CONV_Exceptions", "C:\Documents and Settings\sroark\My Documents\Demands\FC REV\Conv_Exceptions.XLS", True, ""

[/blue]

I live to work and I work to live.

 

[abaldwin]

So what exactly is "lost" EVERY time you open the database?


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[CptCrusty1]

The password for the particular table that this query appends to (in oracle) is completley different than any other password for any other table. REason being, they don't want just anyone appending records to this table... Whenever I make updates to the development copy and move a copy of it to the production folder, we have to re-enter the password for this table EVERY time. Even if we select the "remember password" option when navigating the ODBC link tables process. I need to pass the connection string every time this query runs to avoid this fiasco.

I live to work and I work to live.

 

[abaldwin]

So the password for the ODBC is not kept not the password in the coded connection string?

I would suggest that this is something the DBA will have to solve by setting up a trusted connection or the like for the user that is running the code.


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[JPMontreal]

Then you are using link tables, you will have to delete the link table and redo it, before you run your query, with code that look like that:

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=AAAA;UID=UserName;PWD=XXXx;SERVER=CCCC", acTable, "schema.table_name", "Access_table_name", False, False


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com