I have an access database for a client and it interfaces into another data source. I have an excel program (this is what the clients wishes to use) that is executed nightly in which my VBA code executes a multitude of apend and update query's. These query's access the additional data source and requires a user id and password to be entered.

The current process is as follows: User opens excel program and a series of events automatically begin. Once is reaches a certain point, it executes an append query that interfaces with the additional data source. The user then has to enter his/her id and password. At that point, the program stalls and continues at 3AM once all processing has been completed in the additional data source.

Now, client doesn't want to have a user involved in this first part. They wish for the program to open automatically and run on it's own.

My problem:

I don't know how to write the VBA code so that it executes the query and set's the UserID and Password. I have been researching on my own for a couple of days now. I've read over and over to stay away from SendKeys method. I've been able to find how to set the connection string for database id/password but not at the query level. Can anyone provide some suggestions or help sites that can help me learn how to accomplish this?

Not sure I can help, but I think we'd all have a better shot at doing so if you would show the code you are using now.

For starters, how do you open the connection to the "additional data source"??

My Access database has a linked table to the datasource. The additional datasource has it's own ODBC connection. The datasource name is Velocity. It was developed by a company called AFS. It's very similar to an access database. I think I have found the connection string to use if I was going to use MS query to query against it. This may be of help once I figure things out but for now not sure how it will come into play. My query is in Access and it is an append query. This query retreives information from the linked table and apends to a local table in my database. However, the first time this query is executed, it establishes the connection to the datasource and requires a user id an password. Right now, I'm simply using DAO code that opens the database and peforms dba.execute against the query. A dialog box then appears asking for the user id/pwd. (SQL Servier Login).The datasource is responsible for this not my code.

My boss wanted me to investigate SendKeys and use that but after all the research I did on that, I told him no. It is just too tricky and not a reliable way to go. I would rather spend my time researching another way that would be more dependable and which I could learn something of value that could be used in future projects. I've been programming using access/excel for about three years now but the past couple of months I have only begun to work with linked tables to another ODBC datasource. I'm learning on the fly here and having to teach myself.

I am familiar with code to open Access and provide the id/password when required. But not at the query level. Maybe I should try to figure out a way to open the datasource after access is opened to establish the connection and is there a way to do this using code to connect and provide the id/pwd similar to that of the open code for Access?.....

This turned out to be much more simple that I thought.....When I link the table, I can enter and save the password which eliminates the prompt.... I think this just solved my problem. Have to do some testing but I think this is the simplest answer....