new PC and ODBC issues (1 Viewer)

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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
Perhaps the computer does not have a driver installed, or a different version instead. What is the error message?
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
"ODBC--connection to 'BCC_DB' failed"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
"ODBC--connection to 'BCC_DB' failed"

If you go to the ODBC Connections window from the Control Panel, are you able to see your DSNs and check their connections? If they fail, are you able to create a new one?
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
My usually expected DSNs are missing, I can create one but it fails like above.
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
How about if you tried using a DSN-less connection?

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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
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.
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
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.

Unless I'm missing something... Wow... I have 8 Access databases each with 20+ ODBC connected tables
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
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.
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
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.

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"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
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?
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
Does it work? That's OLEDB, not ODBC. Do you have the driver for it installed?

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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
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
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.
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
I'm confused about how to use the code "If not, try using code to change the connection string in the linked table"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
I'm confused about how to use the code "If not, try using code to change the connection string in the linked table"
Okay, try the following steps:

  1. Make a backup copy of your FE
  2. 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
  3. Pick one of your linked tables and pass its name to the function
Did it work?
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
Okay, try the following steps:

  1. Make a backup copy of your FE
  2. 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
  3. Pick one of your linked tables and pass its name to the function
Did it work?

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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:55
Joined
Oct 29, 2018
Messages
21,358
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
In the Immediate Window, type the following, for example:
Code:
RelinkTable "NameOfYourLinkedTableHere"
 

BennyLinton

Registered User.
Local time
Today, 09:55
Joined
Feb 21, 2014
Messages
263
I get a debug on this line: If .Connect <> "" Then

"Object no longer set"
 

Users who are viewing this thread

Top Bottom