How to quickly change server source for Access Pass through queries.

[Hoardling1 via AccessMonster.com]

I am using Access 2003 and SQL Server 2000. I am going through a conversion
from SQL 2000 - 2008. In Access I use pass throughs to connect to SQL 2000
by ODBC. My problem is I have quite a few pass throughs, and I need to know
is there a quick and easy way to convert them to SQL Server 2008. Note: My
SQL 2008 is on a different server, different name. I could go in manually
and change each one, but I am looking for a mass change. Is it possible?

Thanks.

 

[Mary Chipman [MSFT]]

I'm not sure if I understand what you mean by converting passthrough
queries to SQLS -- they *are* T-SQL statements already ;-) You can
more easily manipulate them in code than you can by hand using DAO
QueryDef objects. You specify the .Connect property to direct to the
server you want to connect to, and the .SQL property to input the
querydef T-SQL statements. Here's a sample of what the code looks
like. -- Mary

Public Sub PassThroughFixup( _
strQdfName As String, _
Optional strSQL As String, _
Optional strConnect As String, _
Optional fRetRecords As Boolean = True)

' Modifies pass-through query properties
' Inputs:
' strQdfName: Name of the query
' strSQL: Optional new SQL string
' strConnect: Optional new connect string
' fRetRecords: Optional setting for ReturnsRecords--
' defaults to True (Yes)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs(strQdfName)
If Len(strSQL) > 0 Then
qdf.SQL = strSQL
End If
If Len(strConnect) > 0 Then
qdf.Connect = strConnect
End If
qdf.ReturnsRecords = fRetRecords
qdf.Close
Set qdf = Nothing
End Sub