Using DSNless connection for linking SQL Server tables to Access

[dr00bie]

Is it possible to use a dsnless connection for linking SQL Server tables in Access? I want to try to get away from ODBC.

Thanks,
Drew

 

[cmmrfrds]

I link to the sql server table through a function with vba code. This function is called in the AutoExec Macro. This way it is not necessary to setup odbc on the clients PC.


Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=rptinvapps;" & _
"Pwd=yourpassword;"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function

 

[dr00bie]

Thank you, I will try that out!

THanks,
Drew

 

[stevenls]

Nice code, but does that mean I have to set up this piece of code
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If

for every table in the database to relink (we have 32 tables)

Thanks

S.

 

[cmmrfrds]

Probably not if all the linked tables are from 1 source. I put the table name in because I had only 1 linked table. If all the tables are from 1 source then just check for type=LINK. Otherwise, maybe there is a way to tell from one of the Jet properties - never tried.