By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

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

P: 1
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.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
  1. Public Sub CreateDatabaseInitial()
  2.  
  3. Dim dbConnectStr As String
  4. Dim Catalog As Object
  5. Dim cnt As ADODB.Connection
  6. Dim dbPath As String
  7. Dim SQL_CHBK As String
  8. Dim dbs As DAO.Database
  9. Dim conn As ADODB.Connection
  10. Dim rst As New ADODB.Recordset
  11. Dim SQLInsert As String
  12. Dim FSO As New FileSystemObject
  13. Dim ProdGroup As Integer    
  14. DoCmd.SetWarnings False
  15.  
  16. Set dbs = CurrentDb()
  17. Set conn = CurrentProject.Connection
  18.  
  19. If Not FSO.FolderExists(FolderCheck) Then MkDir Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & "Transactional Data\"
  20.  
  21. 'Set database name and location here
  22. dbPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & "Transactional Data\" & "Product Set " & [ProdGroup] & ".mdb"
  23. dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
  24.  
  25. 'Create new database – this seems to run fine in access 2003
  26. Set Catalog = CreateObject("ADOX.Catalog")
  27. Catalog.Create dbConnectStr
  28. Set Catalog = Nothing
  29.  
  30. ‘The passthru SQL is very long, but works without issue, so I have not shown it       
  31. SQL_CHBK = "FUNCTIONING PASSTHRU SQL CONTAINED HERE"
  32.  
  33. ‘The below updates the passthru query with the SQL  
  34. dbs.QueryDefs("DATA_ PASS_THROUGH").SQL = SQL_CHBK
  35.  
  36. ‘The below query generates the runtime error when creating a table in a remote mdb
  37. ‘but it runs fine when I change it to place the data in a local table by removing the IN clause
  38. SQLInsert = "SELECT DATA_ PASS_THROUGH.*, * INTO DATA_TRANS IN '" & dbPath & "' FROM DATA_ PASS_THROUGH;"
  39.  
  40. dbs.QueryDefs("DATA_ INSERT").SQL = SQLInsert
  41.  
  42. ‘The below line generates the runtime error
  43. DoCmd.OpenQuery "DATA_ INSERT", acViewNormal, acReadOnly
  44.  
  45. ‘The below code doesn’t get executed in access 2003 because of the runtime error
  46. ‘but it works in access 2007
  47. DoCmd.TransferDatabase acLink, "Microsoft Access", dbPath, acTable, "DATA_TRANS", "DATA_TRANS_" & [ProdGroup]
  48.  
  49. DoCmd.SetWarnings True
  50.  
  51. End Sub
  52.  
  53.  
  54.  
Dec 8 '11 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,186
Your SQL has :
Expand|Select|Wrap|Line Numbers
  1. SELECT [TableName].*
  2.      , *
  3. FROM   [TableName]
  4. 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.
Dec 9 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.