Here's the setup:
MS Access has a linked table to SQL Server. SQL Server has a password on it. Trying to use Excel VBA to query this linked table in Access, but I can't get the connection string correct. I can connect from Excel to Access, or from Excel to SQL Server, but I can't figure out how to go Excel --> MS Access --> SQL Server. Need help.
Thanks for your time.
Please share the connection strings you are using. There are two separate right? One for Excel -> Access and one for Access -> SQL Server.
Is there any error? Please update your with error messages. Or specify more precisely the problem / what's happening when connecting? Right now it's a bit to vague I think...
Ooop. Sorry about that. I only have one connection string (from Excel to Access, with Access already having a linked table to SQL Server established). I'm not sure how to add a second connection string. Basically, when I go to open the linked table in Access, a prompt window pops up that asks for the Login ID and Password for the SQL Server. Here's the code I'm using so far:
Function DatabaseCall2(ByVal sSQL1 As String) As ADODB.Recordset Dim Cn As ADODB.Connection Dim RS as ADODB.RecordSet Dim ConnectionString As String Set Cn = New ADODB.Connection 'DSN=FIDM_Reporting 'DATABASE=fi_analytics_reporting ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=FIDM_Reporting;" & _ "User ID=UserNameHere;Password=Passwordhere" Cn.ConnectionString = ConnectionString Cn.Properties("Prompt") = adPromptAlways Cn.Open // Will add code here to execute SQL statement and assign RS, but I haven't gotten past the previous step End function
When I run this code, I get the prompt that asks for Data Source, User Name, Password, Provider String, and Open Mode. I've been Googling this for a few hours, and I'm not sure what to put in for Data Source. Is it the SQL Server DSN? What about Provider String?
Remove (or comment out) the line
Cn.Properties("Prompt") = adPromptAlways
to get rid of the prompt. (cause its the prompting thats your problem right?)