ADODB Connection String with Linked SQL Server Table

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?)