troubleshooting Question

DSNless ODBC Connection to SQL Server from Acess for Scalable Updates

Avatar of kabradley
kabradley asked on
Microsoft AccessMicrosoft SQL Server
24 Comments1 Solution709 ViewsLast Modified:
Hello Everyone,

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

cnn.Open _
 "DRIVER=SQL Server;" & _
 "Server=xxxserversaddressxxx;" & _
 "Database=DBname;UID=xxxx;PWD=xxxx"
 
Debug.Print cnn.ConnectionString

End Sub

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?
AND
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)
ASKER CERTIFIED SOLUTION
jmoss111

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 24 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 24 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros