ODBC Connection Dynamic From Clause

Question

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.

Andre · Accepted Answer · 2016-02-02T14:18:15.067

2

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

edited Feb 02 '16 at 14:18

answered Feb 02 '16 at 13:27

Andre

26,751
7
36
80

I understand everything you are saying. I have used the same method successfully elsewhere in the database but only with MS tables. The catch seems to be the pass-thru query via the FoxPro ODBC. This whole process is importing the data from a FoxPro DB to a Access DB. The "Test" folder in the FoxPro DB can have any one of 5 collections and therefor will fail with static table names. I am looking now at how to do a passthru in VBA. – Kaw4Life Feb 02 '16 at 13:43
@Kaw4Life: It's the same method. Set up your PassThrough query with the ODBC connection, then you can do `DB.QueryDefs("myPassThroughQuery").SQL = strSql` in VBA. But you cannot use functions in PassThrough queries, the SQL is sent to the DB server as is. – Andre Feb 02 '16 at 14:12
I've got something working. Once refined/complete I will post for others to use, God forbid they need to. – Kaw4Life Feb 02 '16 at 14:57

score 0 · Answer 2 · answered Feb 05 '16 at 13:10

First, you shouldn't use ODBC with VFP, unless you are using VFP6 and earlier or ADS server. Instead simply use OLEDB (VFPOLEDB). Then you can pass your function's result as a parameter. ie:

Select field1, field2 field3 From (?)

and add funTestShipUic() to parameters collection. If you used ODBC then in a passthrough query it would be like:

Select field1, field2 field3 From (?funTestShipUic())

Parameters are handled by the drivers and SQL is not sent to the server as is.

Thank you for your input. I have a solution in place with consideration to all facets of the given the environment. — Kaw4Life, Feb 08 '16 at 00:44

ODBC Connection Dynamic From Clause

2 Answers2