I am needing to connect to a SQL server being hosted on a remote webserver from my access database. The goal of logging in is this:
1. Open Access
2. Prompt User for user ID and PW
3. If UID & PW are correct, check program front end version (stored in local table)
4. If version is lower then current version have client's access run a pass through query to grab the data on the SQL server that is new
5. Run an append query on the clients machine based off of the pass through query that updates their tables with the new information. The client does not have permissions to edit the tables that update.
6. Close ODBC Connection
I'm just having a plethora of problems getting this to work. First and foremost is the DSNless connection. I have a module with the following code
Public Sub UseConnectionODBC()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
"DRIVER=SQL Server;" & _
"Server=xxxserversaddressxxx;" & _
I then call this function on the On_Open event of the dashboard/switchboard form that opens with the program. I then tried making a pass through query that has the following sql:
SELECT * From LPID;
The first problem I've run into is that when I try and run this query, Access immediately prompts me for the proper DSN. I don't know why b/c technically by my understanding, I'm currently connected to the SQL Server. (All of the information in the connection function is accurate).
I then tried to query the pass-through query to see if that was the problem. Access wouldn't even let me select the query in the table chooser until I selected the pass-through queries associated DSN.
My two questions are:
1. What am I doing wrong? Why is the query not working when the connection should be established?
2. Is there a better method of updating the client's data? (The clients data will need updates roughly 10 or so times a month)