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.TransferDatabase 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_THROUGH.*, * INTO DATA_TRANSACTIONS IN '\\Folder1\Folder2\Folder3\Transactional Data\Product Set 1.mdb' FROM DATA_PASS_THROUGH;
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.
Expand|Select|Wrap|Line Numbers
- 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