473,320 Members | 2,145 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
1 2444
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

42
by: aaron.kempf | last post by:
since Access 2007 is a 'major change' then i assume that MS is going to make a new newsgroup for it. I mean; when VB.net 2002 came out; they decided to rename the 'main vb newsgroup' as...
1
by: Dave | last post by:
Hello All, I have Access 2007 on my home machine and use Access 2003 at work. Are there any options available to downgrade my home machine to 2003 (or run both versions) that don't involve...
1
by: hnpatel | last post by:
Hi to all, How to use insert query in php? I m using ODBC connection with access database.I want to save data in table. I m using database 'PHPDB1'. Table Name 'EDETAIL'. There r...
0
by: Walter Konopacki | last post by:
Does anyone know if the problems displaying OLE objects in forms and reports has been addressed in Access 2007. Want to upgrade if it has been resolved. Any help greatly appeciated.
0
by: WPW07 | last post by:
Hello Everyone, I stumbled onto something interesting and was wondering if anyone else has experienced it and how they fixed it. I've posted to several forums to no avail. I've got a...
0
by: susan | last post by:
i am a bear of very little brain so please don't use too many abbreviations or technical jargon or it might melt alltogether... I have created a database with Access 2007 and saved it, via the...
1
by: kfry | last post by:
I am having an issue maybe someone can help with. I have written an application with Access 2003 on my XP pc. I then package it and install it on my VISTA Access 2007 PC. When I do this the wrong...
1
by: tbeers | last post by:
Good morning. I have an interesting chain of events. My 2007 program has been working fine on a network. I have a split database with tables on F:\timekeeper and front end on terminal server...
2
by: gabielmatos | last post by:
this is my query; string NPI = fields.GetValue(0).ToString(); string EntiType = fields.GetValue(1).ToString(); string ProvLastNameLegal = ...
1
by: lisaflynn | last post by:
Just upgraded to Access 2007 from 2003....In the older version you could create a query relating to the table you had open just by opening a query while being in the table. I can't figure out a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.