VBA generated INSERT query works in access 2007 but not access 2003

[JD79]

I have an access 2007 database (on an XP machine) that extracts a large set of data from an Oracle db using ODBC. The data extraction is done with a dynamic passthru query that is then called in a make table query that inserts the data into a new table in another access database file. The remote mdb file is also created on the fly when code is run.

The passthru code is dynamic, with the SQL string being assigned to a variable that is then inserted into a passthru query using the querydef() function. VBA is also used to generate the make table query that then inserts the results of the passthru into a table in another mdb file. VBA is used to create this make table query because the location and name of the remote database is dynamic. The querydef function is used to place the INSERT code into a query, and the docmd.openquery function is used to run it. The DoCmd.TransferD atabase function is then used to link the remote table containing the data to the main mdb file that is executing the code.

This code has always worked on my XP machine using access 2007 and running on my local harddrive. However, when I tried running the code in the below scenarios I received the "Query input must contain at least one table or query" runtime error when the make table query is run.

1. I tried to run the code on another computer running access 2003 in Windows 7 with the main mdb file on a network drive. This creates the remote mdb file, but then generates the above error when the make table is run.

2. I then tried to run the code on a local HD in access 2003 on a Windows 7 machine. Again, the remote mdb file is created, but the make table query generates the above error.

3. Next I tried running it in access 2003 on an XP machine and still got the error. The remote mdb file is created, but the make table query still generates the above error.

4. I changed the make table query to insert the results of the passthru into a LOCAL table, not a table in a remote db, and the query ran successfully. I take this to mean that the SQL used in the passthru is ok.

Given that the error is generated on both Windows 7 and XP, whether on a network drive or local drive, it SEEMS that the issue is somehow related to access 2003. In all cases in which the runtime error was generated, the code did successfully create the remote mdb file. There just seems to be an issue with creating the new table in the remote mdb. I know there is an issue with using the Jet provider on Windows 64bit machines, but this code is also failing on XP machines running access 2003.

I have the below references checked in access 2007. When I view the references while running the code on the other computers in access 2003 the same references are checked, though the version numbers are lower.
Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Object 2.1 Library
Microsoft Scripting Runtime

When I insert a debug.print to show the make table code that is generating the error I get:

SELECT DATA_PASS_THROU GH.*, * INTO DATA_TRANSACTIO NS IN '\\Folder1\Fold er2\Folder3\Tra nsactional Data\Product Set 1.mdb' FROM DATA_PASS_THROU GH;

The above looks fine, and runs fine in access 2007 on my XP machine. It just doesn't seem to run on any computer running access 2003. I can't figure out why.

Line 43 generates the "Query input must contain at least one table or query" error. Lines 36 - 43 are related to the query that is giving me trouble.

If anyone has any thoughts as to why I'm getting this error in access 2003 I'd love to hear them.

Public Sub CreateDatabaseInitial()
 
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
Dim SQL_CHBK As String
Dim dbs As DAO.Database
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim SQLInsert As String
Dim FSO As New FileSystemObject
Dim ProdGroup As Integer    
DoCmd.SetWarnings False
 
Set dbs = CurrentDb()
Set conn = CurrentProject.Connection
 
If Not FSO.FolderExists(FolderCheck) Then MkDir Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & "Transactional Data\"
 
'Set database name and location here
dbPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & "Transactional Data\" & "Product Set " & [ProdGroup] & ".mdb"
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
 
'Create new database – this seems to run fine in access 2003
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create dbConnectStr
Set Catalog = Nothing
 
‘The passthru SQL is very long, but works without issue, so I have not shown it       
SQL_CHBK = "FUNCTIONING PASSTHRU SQL CONTAINED HERE"
 
‘The below updates the passthru query with the SQL  
dbs.QueryDefs("DATA_ PASS_THROUGH").SQL = SQL_CHBK
 
‘The below query generates the runtime error when creating a table in a remote mdb
‘but it runs fine when I change it to place the data in a local table by removing the IN clause
SQLInsert = "SELECT DATA_ PASS_THROUGH.*, * INTO DATA_TRANS IN '" & dbPath & "' FROM DATA_ PASS_THROUGH;"
 
dbs.QueryDefs("DATA_ INSERT").SQL = SQLInsert
 
‘The below line generates the runtime error
DoCmd.OpenQuery "DATA_ INSERT", acViewNormal, acReadOnly
 
‘The below code doesn’t get executed in access 2003 because of the runtime error
‘but it works in access 2007
DoCmd.TransferDatabase acLink, "Microsoft Access", dbPath, acTable, "DATA_TRANS", "DATA_TRANS_" & [ProdGroup]
 
DoCmd.SetWarnings True
 
End Sub
 
 
 

[NeoPa]

Your SQL has :

SELECT [TableName].*
     , *
FROM   [TableName]
etc

Can you see that only one of the *s should be there. If 2007 allowed you that SQL then it was clumsy to do so. That SQL is not correct.

PS. Congratulations on putting so much effort (and it was well organised too) into your question. I'm tired now, but I thought that much effort deserved some help. Good for you.