Search code examples
ms-accessms-access-2010visual-foxpropass-through

ODBC Connection Dynamic From Clause

ODBC;DSN=Test;UID=;PWD=;SourceDB=\\server\folder\Test\prime.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

Then the functional static query that uses that connection. There are 5 tables in prime.dbc.

Select field1, field2 field3
From Table1

Now based on the function below I want to feed funTestShipUic to the FROM clause instead of the static Table1.

Public Function funTestShipUic() As String

   funTestShipUic = lngTestShipUic

End Function

I have tried ...

Select field1, field2 field3
From funTestShipUic()

But I get ODBC--call failed. ODBC Visual FoxPro Driver Invalided subscript reference.

Solution

  • It's hard to understand what you are actually trying to achieve.

    You can build dynamic SQL in VBA with string concatenation, like this:

    strSql = "SELECT field1, field2 FROM " & myFunctionThatReturnsATableName()
Set myRecordset = CurrentDb.OpenRecordset(strSql)

    You can also assign that SQL to an existing query, including PassThrough queries.

    DB.QueryDefs("myQuery").SQL = strSql