error 3151

[Guest]

I have moved old queries to a new server and the SQL database with the linked
tables to the same server. Now when I run the I get an error advisory <ODBC
-- connection to 'imis 10 failed>. The ODBC on the PC pointing to the SQL
database on the new server has been changed to reflect the new location.

How do I get the ACCESS query to see and use the new database connectivity?

 

[Gary Walter]

I have moved old queries to a new server and the SQL database with the
linked
tables to the same server. Now when I run the I get an error advisory
<ODBC
-- connection to 'imis 10 failed>. The ODBC on the PC pointing to the SQL
database on the new server has been changed to reflect the new location.

How do I get the ACCESS query to see and use the new database
connectivity?

Hi John,

Unless my newgroup reader is letting me down
again, it looks to me like you have not received
a reply.....

one tends to shy away from questions such as above
because "queries/SQL database/linked tables/ODBC"
has so many "paths."

In my little world, I use DSN's and in my mdb's I
link to views/tables. Except for the passthrough queries
to stored procedures, all my other queries use the
linked views/tables.

If you are in a similar situation, then the solution is easy.

You need to delete the links and recreate them.

Actually, in all our mdb's, I have a combobox with
names of production and testing DSN's, and in the
after_update event of combobox, I run version of
Joe Fallon's code to delete the links and then recreate
them using a table ("tblODBCLinks"). This table has
2 fields:

'tblODBCTables
' LinkTableName Ex: "VW_GW_SOPOInfo"
' LinkIndex Ex: "TermID, PONumber, SKU"

If LinkTableName is a View, then the LinkIndex
is used to create a uniqueindex for the View.

If LinkTableName is a table, then LinkIndex
is left blank.

Joe's code also refreshes connection string for passthroughs.

Plus, in the end, I run code to "turn off subdatasheets"
using code from

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q261000

Besides switching between test and production server,
all the above code gets run when views get changed.

So.....
if your "queries/SQL database/linked tables/ODBC"
runs down this same "path,"

search Google Groups for

Joe Fallon relink SQL Server group:microsoft.public.access.*

to get code that will automatically do this...

or, just manually do it.

 

[Gary Walter]

just to show where "create unique index"
and "TurnOffSubDataSheets" work in my
code that may differ from Joe's:

(obviously this is not code you just copy into
a form module and it works...but hope it may help!)

'********************************************************
'*** Reconnect tables based on info in tblODBCTables ****
'********************************************************
'tblODBCTables
' LinkTableName Ex: "VW_GW_SOPOInfo"
' LinkIndex Ex: "TermID, PONumber, SKU"
Set rs = dbs.OpenRecordset("tblODBCTables")

'open db connection w/new connection string
'(new string will have selected DSN, and possibly new UID, and possibly new PWD)
Set dbsODBC = OpenDatabase("", False, False, m_SQLConnect)
'Debug.Print "dbsODBC.Connect = " & dbsODBC.Connect

Me.lblMsg.Caption = "Recreating Table Links to SQL Server .. . ."
Me.Repaint

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef("dbo_" & rs![LinkTableName], dbAttachSavePWD)
'*****************************************************
'*** set this table's connection to new connection ***
'*****************************************************
tdfAccess.Connect = dbsODBC.Connect
'SourceTableName needs "dbo." prefix
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." & rs![LinkTableName]).Name
dbs.TableDefs.Append tdfAccess

'if View, will need to create _uniqueindex (stored in tblODBCTables.LinkIndex)
If Len(Trim(rs!linkindex & "")) > 0 Then
strSQL = "CREATE INDEX _UniqueIndex ON dbo_" & rs!LinkTableName & " (" & rs!linkindex & ")"
CurrentDb.Execute strSQL, dbFailOnError
Else
'no unique index in tblODBCTables for this linked table
End If

rs.MoveNext
Loop

' Refresh Connect String for all Pass-Through Queries
Me.lblMsg.Caption = "Refreshing links for all Pass Through Queries . . ."
Me.Repaint

For Each qdf In dbs.QueryDefs
If Len(qdf.Connect) > 0 Then
qdf.Connect = m_SQLConnect
End If
Next


rs.Close
dbs.Close
dbsODBC.Close

'****************************************
'***** end ReLinking to SQL Server ******
'****************************************
'refresh tabledefs
CurrentDb.TableDefs.Refresh

'show how ODBC now connecting
strConn = CurrentDb.TableDefs("dbo_VW_GW_SO").Connect
'get UID from current connection string
intPos = InStr(1, strConn, "UID=", vbTextCompare)
If intPos > 0 Then
strUID = Mid(strConn, intPos + 4)
intPos = InStr(1, strUID, ";", vbTextCompare)
If intPos > 0 Then
strUID = Left(strUID, intPos - 1)
m_UID = strUID
Me!txtUID = m_UID
Else

End If
Else

End If

'get PWD from current connection string
intPos = InStr(1, strConn, "PWD=", 1)
If intPos > 0 Then
strPWD = Mid(strConn, intPos + 4)
intPos = InStr(1, strPWD, ";", vbTextCompare)
If intPos > 0 Then
strPWD = Left(strPWD, intPos - 1)
m_PWD = strPWD
Me!txtPWD = m_PWD
Else

End If
'get rid of PWD=xxxx; if it exists before displaying connection
intPos = InStr(1, strConn, "PWD=", 1)
strConn = Left(strConn, intPos - 1) & Mid(strConn, InStr(intPos, strConn, ";", 1))
Else

End If

Me.txtConnection = strConn
Me.txtConnectionLogin = strConn
Me.txtServer = m_ServerName
Me.txtServerLogin = m_ServerName
Me.Repaint

Me!cmboDSN = Null

'make sure SubDataSheets is not [Auto] for newly-connected tables
TurnOffSubDataSheets

Me.lblMsg.Caption = "Have successfully switched to DSN = " & m_DSN & " . . . please go back and login"
Me.Repaint