I wrote a 'VBA' that the lab has been using for 2 years that basically used an access database as a query. Recently, a SQL database is being used and an ODBC was created to use in the query. The problem is that query can not been seen by excel, except for my computer. I have SQL Management Studio 2008 on mine, but I can not seem to get the query working on other computers. I have tried different drivers but can not solve this.
-
Please add code examples and error messages or a more detailed description of the unexpected behavior.Brian Pressler– Brian Pressler2015-09-03 23:02:08 +00:00Commented Sep 3, 2015 at 23:02
-
I am not in the office and will post the exact error tomorrow, but is basically not able to find an odbc driver. I have tried the ms access redistributable driver and the sqlmanagement.msc in the system32 folder, but that does not help. Thank you :).justaguy– justaguy2015-09-03 23:15:57 +00:00Commented Sep 3, 2015 at 23:15
-
So you were querying Access from Excel and now you're going from Excel to SQL Server? Sounds like it might be a permission issue.Hannover Fist– Hannover Fist2015-09-03 23:18:50 +00:00Commented Sep 3, 2015 at 23:18
-
How do I add the ODBC so that excel can access it? Thank you :).justaguy– justaguy2015-09-03 23:30:26 +00:00Commented Sep 3, 2015 at 23:30
-
1You need to add a system DSN (data source name) on every computer. Alternatively use a DSN-less connection in MS Access: support.microsoft.com/en-us/kb/892490. Try out the linked methods and you won't have to deploy a DSN to every computer that needs to use it. In future consider moving away from MS Access and ODBCNick.Mc– Nick.Mc2015-09-04 00:37:53 +00:00Commented Sep 4, 2015 at 0:37
Add a comment
|
1 Answer
It's not 'an ODBC driver' that cannot be found; it's the DSN file that you created (with credentials) that is local to your computer. You need to spell everythign out in a connection string that will be carried around in the VBA module.
Sub sql_connect()
Dim svr As String, db As String, usr As String, pwd As String
Dim sCNX As String, sSQL As String
Dim cnx As Object, rs As Object
Set cnx = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
svr = "THE_SERVER"
db = "myDatabase"
usr = "me_user"
pwd = "me_users_pwd"
sCNX = "Provider=SQLNCLI11;Server=" & svr & ";Database=" & db & ";UID=" & usr & ";PWD=" & pwd & ";"
'Debug.Print sCNX 'used for syntax checking
cnx.Open sCNX
sSQL = "SELECT TOP 50 * FROM [myDatabase].dbo.[myTABLE]"
rs.Open sSQL, cnx
Do While Not rs.EOF
Debug.Print rs(0) & " - " & rs(1)
rs.movenext
Loop
rs.Close: Set rs = Nothing
cnx.Close: Set cnx = Nothing
End Sub
There are ways to hide the credentials that you are providing; locking your VBA project with a password would be a start. Creating an MSSQL user with limited, read-only access is also a good step.
I'm not sure if it is apparent, but that user and password as SQL user credentials, not a domain user using a trusted connection.
1 Comment
justaguy
Thank you all for the help and for introducing me to a DSN-less connection, works great :).