Pass through query with a stored password

[Karen831]

I'm trying to write a pass through query (first time) using access 2003.

I need to store the ODBC password within my query so when it run my autoexec macro it does not prompt for a password.

I'm having two issues:

#1 - my pass through query is referring referring to a table per below but it is giving me an ODBC error.
#2 - I don't know the language of how to store the password. (In some cases, I have two passwords for two different systems that I am grabbing data from)

HELP!
Thanks!

Table Name - LENOX_VIEW_BRANDS_SHIP_REC
I want all fields from this table to be dumped into this table - Shippingdetail

This is my sql: (that is not working)

SELECT LENOX_VIEW_BRANDS_SHIP_REC.* INTO Shippingdetail
FROM LENOX_VIEW_BRANDS_SHIP_REC;

 

[MMaynard]

Your user and password are stored in the connection string.

DSNConnection = "ODBC;DSN=DSNNAME;UID=UserName;PWD=UserPassword;DATABASE=DatabaseName;Network=DBMSSOCN;Address=ServerNameOrAddress;Trusted_Connection=No"

 

[datAdrenaline]

>> #1 - my pass through query is referring referring to a table per below but it is giving me an ODBC error. <<

Your Passthrough Query has no clue as to what Access Objects are, so you can not refer to them within your PT queries SQL statement. If you PT query records records, you CAN use the PT query in a Access Query -- in your case a Make Table query and your SQL statement (as viewed by looking at the SQL View of the Query Object) would be something like this:

SELECT nameOfPTQueryObject.* INTO ShippingDetail FROM nameOfPTQueryObject


>> #2 - I don't know the language of how to store the password <<

You simply set the ODBC Connect property of the Passthrough Query object to string value that contains the UID and PWD. There is really no need for code.


>> In some cases, I have two passwords for two different systems that I am grabbing data from <<

Then, if you want, create a PT query for each source, then Join them in your standard Access Query Object.

There are other options, for example, you can create linked tables to your multiple sources, then use your linked tables in your Access Query Object.

 

[datAdrenaline]

post to "bump", so the change to my previous post is noted by the OP.