BennyLinton
Registered User.
- Local time
- Today, 09:55
- Joined
- Feb 21, 2014
- Messages
- 263
I just got a new PC at the office and my old ODBC connections to SQL Server are failing. I tried loading the registry files but no luck... any ideas?
"ODBC--connection to 'BCC_DB' failed"
My usually expected DSNs are missing, I can create one but it fails like above.
How about if you tried using a DSN-less connection?
No, not exactly. I mean, for each linked table, your connection to the ODBC source is embedded within the connection string of its Connect property.You mean having each of the dozens of adds, updates, deletes, etc. have code to open and close the database like one would use in a .net application?
No, not exactly. I mean, for each linked table, your connection to the ODBC source is embedded within the connection string of its Connect property.
Hi. It's not really much different than what you're doing now. The only difference is rather than have a DSN, you won't have any. And all you need to make it work is some code to go through all your linked tables and replace the connection string with a working version. If you're not familiar with DSN-less connection, take a look at this website.Unless I'm missing something... Wow... I have 8 Access databases each with 20+ ODBC connected tables
Hi. It's not really much different than what you're doing now. The only difference is rather than have a DSN, you won't have any. And all you need to make it work is some code to go through all your linked tables and replace the connection string with a working version. If you're not familiar with DSN-less connection, take a look at this website.
Does it work? That's OLEDB, not ODBC. Do you have the driver for it installed?I have with a module this connection string already:
Option Compare Database
Public Const CONN_STRING As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GCDF_DB;Data Source=BADLANDS"
Does it work? That's OLEDB, not ODBC. Do you have the driver for it installed?
Well, I haven't tried using that specific connection string to link a table, but I guess you could give it a try. For example, if you double-click on any of your linked tables from the Navigation Pane, are you able to see the data? If not, try using code to change the connection string in the linked table with the one you're using in your code, and then try double-clicking on the table again to see if it works.Only when using code for a Sub like below:
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con
cmd.CommandType = adCmdText
Okay, try the following steps:I'm confused about how to use the code "If not, try using code to change the connection string in the linked table"
Public Sub RelinkTable(TableName As String)
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs(TableName)
With tdf
If .Connect<>"" Then
.Connect="YourConnectionStringHere"
.RefreshLink
End If
End With
Set tdf = Nothing
End Sub
Okay, try the following steps:
Did it work?
- Make a backup copy of your FE
- In a Standard Module, enter the following code:
Code:Public Sub RelinkTable(TableName As String) Dim tdf As DAO.TableDef Set tdf = CurrentDb.TableDefs(TableName) With tdf If .Connect<>"" Then .Connect="YourConnectionStringHere" .RefreshLink End If End With Set tdf = Nothing End Sub
- Pick one of your linked tables and pass its name to the function
In the Immediate Window, type the following, for example:How do I make this code below actually run... and do I put my table's name in place of "TableName"?
Option Compare Database
Public Const CONN_STRING As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GCDF_DB;Data Source=BADLANDS"
Public Sub RelinkTable(TableName As String)
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs(TableName)
With tdf
If .Connect <> "" Then
.Connect = CONN_STRING
.RefreshLink
End If
End With
Set tdf = Nothing
End Sub
RelinkTable "NameOfYourLinkedTableHere"