MS-Access 2007 ODBC error with pass-through queries

[JimP]

When linking SQL Server tables as pass through queries in MS-Access 2007,
error 3146 ODBC Call Failed is returned when trying to open the table.

The same link does not produce an error in MS-Access 2003.

Is this a configuration setting in 2007?

 

[Alex Dybenko]

Hi,
can you be more specific? your using linked table or pass-through query?
what is SQL?

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[datAdrenaline]

Along with Alex's questions ... I was wondering what your connection string
looks like? ...

 

[JimP]

Along with Alex's questions ... I was wondering what your connection string
looks like? ...

 

[JimP]

Thanks,

Code is below. Table [SYSADM_TablesUsed] stores a list of SQL Server table
names, then the the code loops through linking each table as a pass through
query. The connect string passed from the form is
"ODBC;DSN=DEMO583;UID=SYSADM;PWD=sysadm". This sequence works fine in
MS-Access 2003, but produces an error in Access 2007. The error occurs when
openening the query.

Function SetQueryConn_qdf()
Dim db As Database, rs As Recordset
Dim Quy As QueryDef
Dim qdfSYSADM_Tables As QueryDef
Dim qdfNewQry As QueryDef
Dim strConnString As String
Dim strSQL As String, strAccessName As String, strAccessSQL As String
On Error GoTo error_handler

Set db = CurrentDb
strConnString = Forms![frmRuntimeSpecs]!ADVAN_DSN

' Set up the queries for the SYSADM tables, using SYSADM_TablesUsed
strSQL = "SELECT [SYSADM_TableName] FROM [SYSADM_TablesUsed]"
Set qdfSYSADM_Tables = db.CreateQueryDef("", strSQL)
Set rs = qdfSYSADM_Tables.OpenRecordset()
If rs.BOF And rs.EOF Then Exit Function
rs.MoveFirst
While Not rs.EOF
strAccessName = "SYSADM_" & rs(0)
'Deletes the tables before re-linking
On Error Resume Next
DoCmd.DeleteObject acQuery, strAccessName
'Links the table
strAccessSQL = "SELECT * FROM dbo." & rs(0)
On Error GoTo error_handler
Set qdfNewQry = db.CreateQueryDef(strAccessName, strAccessSQL)
qdfNewQry.Connect = strConnString
'Debug.Print rs(0)
rs.MoveNext
Wend
RefreshDatabaseWindow
Exit Function

error_handler:
Debug.Print Err.Number, Err.DESCRIPTION
End Function

 

[JimP]

I found a solution, but I don't understand the problem. When using
"CreateQueryDef", MS-Access 2007 converts

strSQL = "SELECT * FROM dbo.TABLE_NAME" to
strSQL = "SELECT * FROM (dbo)TABLE_NAME" and creates an error

...when strSQL is used in the CreateQueryDef("QueryName", strSQL) statement

Applying strSQL as a separate statement resolves the problem, e.g.

Set qdf = db.CreateQueryDef("QueryName")
qdf.SQL = strSQL

Thanks,

Code is below. Table [SYSADM_TablesUsed] stores a list of SQL Server table
names, then the the code loops through linking each table as a pass through
query. The connect string passed from the form is
"ODBC;DSN=DEMO583;UID=SYSADM;PWD=sysadm". This sequence works fine in
MS-Access 2003, but produces an error in Access 2007. The error occurs when
openening the query.

Function SetQueryConn_qdf()
Dim db As Database, rs As Recordset
Dim Quy As QueryDef
Dim qdfSYSADM_Tables As QueryDef
Dim qdfNewQry As QueryDef
Dim strConnString As String
Dim strSQL As String, strAccessName As String, strAccessSQL As String
On Error GoTo error_handler

Set db = CurrentDb
strConnString = Forms![frmRuntimeSpecs]!ADVAN_DSN

' Set up the queries for the SYSADM tables, using SYSADM_TablesUsed
strSQL = "SELECT [SYSADM_TableName] FROM [SYSADM_TablesUsed]"
Set qdfSYSADM_Tables = db.CreateQueryDef("", strSQL)
Set rs = qdfSYSADM_Tables.OpenRecordset()
If rs.BOF And rs.EOF Then Exit Function
rs.MoveFirst
While Not rs.EOF
strAccessName = "SYSADM_" & rs(0)
'Deletes the tables before re-linking
On Error Resume Next
DoCmd.DeleteObject acQuery, strAccessName
'Links the table
strAccessSQL = "SELECT * FROM dbo." & rs(0)
On Error GoTo error_handler
Set qdfNewQry = db.CreateQueryDef(strAccessName, strAccessSQL)
qdfNewQry.Connect = strConnString
'Debug.Print rs(0)
rs.MoveNext
Wend
RefreshDatabaseWindow
Exit Function

error_handler:
Debug.Print Err.Number, Err.DESCRIPTION
End Function

Along with Alex's questions ... I was wondering what your connection string
looks like? ...