CODEWITHSUNDEEP
ms-access
gregg
gregg

Reputation: 1124

Variable in pass-through query 'ODBC Connect Str'

I am trying to use a global constant variable (within a standalone module) within the property 'ODBC Connect Str' within a SQL Pass-Through Query in Microsoft Access. To be clear I am using the GUI, I understand how I would do it in VBA. It works if I don't use the varible, here is that connection string:

ODBC;Driver=SQL Server;Server=pabxsoftdrt;Database=DRT;Trusted_Connection=True;

I am trying to replace the Server value with constant drtHostname. Things I have tried without success (I omitted the Trusted_Conection for ease of reading):

• ODBC;Driver=SQL Server;Server=drtHostname;Database=DRT;
• ODBC;Driver=SQL Server;Server=" & drtHostname & ";
• "ODBC;Driver=SQL Server;Server=" & drtHostname & ";"
• ODBC;Driver=SQL Server;Server=(drtHostname);
• ODBC;Driver=SQL Server;Server=drtHostname();
• ODBC;Driver=SQL Server;Server=" & drtHostname() & ";
• "ODBC;Driver=SQL Server;Server=" & drtHostname() & ";"
• ODBC;Driver=SQL Server;"Server=" & drtHostname & ";"

Upvotes: 0

Views: 2684

Answers (2)

donPablo
donPablo

Reputation: 1959

Alternatively, you could put it all in the SQL and replace it before you execute

    where ptqSQL has
    .. .. ..
    From  
    [ODBC;Driver=SQL Server;Server=@Svr1;Database=@Db1;].dbo.Tbl1 as T1

    Left Join   (Select * From
    [ODBC;Driver=SQL Server;Server=@Svr2;Database=@Db2;].dbo.Tbl2 as T2

Then after getting sSQL = CurrentDb.QueryDefs("ptq_SQL").sql

do the replaces (Svr, Db, Tbl may already be explicit in the ptq_SQL)

    sSQL = Replace(sSQL, "@Svr1", "xxx:)
    sSQL = Replace(sSQL, "@Db1", "xxx:)
    sSQL = Replace(sSQL, "@Tbl1", "xxx:)
    sSQL = Replace(sSQL, "@Svr2", "xxx:)
    sSQL = Replace(sSQL, "@Db2", "xxx:)
    sSQL = Replace(sSQL, "@Tbl2", "xxx:)

and then execute it

    Dim rst As ADODB.Recordset, nRecAffected As Long
    Set rst = yourConnection.Execute(sSQL, nRecAffected)

Upvotes: 0

HansUp
HansUp

Reputation: 97131

I think your question is about the QueryDef.Connect property for a pass-through query. I don't believe it's possible for that property to reference a variable.

Your connection strings could use a DSN instead and then you revise the DSN whenever the server name changes. But all your users would need that DSN on their machines.

Or you could use VBA to update the QueryDef.Connect property when needed. Here is an Immediate window example of a function which alters the server piece in a connection string.

strConnect = "ODBC;Driver=SQL Server;Server=pabxsoftdrt;" & _
    "Database=DRT;Trusted_Connection=True;"
drtHostname = "newserver"
? ChangeServer(strConnect, drtHostname)
ODBC;Driver=SQL Server;Server=newserver;Database=DRT;Trusted_Connection=True;

So you could call the function to update your saved pass-through query's connection string ...

With CurrentDb.QueryDefs("YourQuery")
    .Connect = ChangeServer(.Connect, drtHostname)
End With

The function's job is easy with a regular expression.

Public Function ChangeServer(ByVal pConnect As String, _
        ByVal pServer As String) As String

    Dim re As Object ' RegExp
    Set re = CreateObject("VBScript.RegExp")
    re.IgnoreCase = True
    re.pattern = "Server=[^;]*"
    ChangeServer = re.Replace(pConnect, "Server=" & pServer)
End Function

Upvotes: 1

Related Questions