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

Nested VBA append query doesn't append

jmoudy77
P: 20
I've been using queries in VBA for a while and usually have a pretty good handle on them, but this one is kicking my butt. Using Access 2007.

The form is designed to export(append) records from tables in the current database to identical tables in another database.

I know the loop is working because the table names are printed to the immediate window, but when I check the database being appended to the information isn't there. I am also not recieving any error messages; I'm stumped.

Pretty straight forward:

where txtto and txtfrom are textboxes

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdimport_Click()
  2.  
  3.     Dim i As Integer
  4.     Dim fromdb As String
  5.     Dim todb As String
  6.  
  7.     On Error Resume Next
  8.  
  9.     todb = Me.txtto
  10.     fromdb = Me.txtfrom
  11.  
  12.     Set Db = OpenDatabase(fromdb)
  13.     For i = 0 To Db.TableDefs.Count - 1
  14.         If Mid(Db.TableDefs(i).Name, 1, 4) <> "MSys" Or "USys" Then
  15.             DoCmd.RunSQL "INSERT INTO Db.TableDefs(i).Name IN fromdb SELECT AVSTATS.* FROM Db.TableDefs(i).Name);"
  16.             Debug.Print Db.TableDefs(i).Name
  17.         End If
  18.     Next
  19.  
  20. End Sub
  21.  
Thanks in advance
Dec 8 '08 #1
Share this Question
Share on Google+
3 Replies


jmoudy77
P: 20
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdimport_Click()
  2.  
  3.     Dim i As Integer
  4.     Dim fromdb As String
  5.     Dim fromdb2 As String
  6.     Dim todb As String
  7.  
  8.  
  9.     On Error Resume Next
  10.  
  11.     todb = "'" & Forms![AMS Import Utility].txtto & "'"
  12.     fromdb = Forms![AMS Import Utility].txtfrom
  13.     fromdb2 = "'" & Forms![AMS Import Utility].txtfrom & "'"
  14.  
  15.  
  16.     Set Db = CurrentDb()
  17.     For i = 0 To Db.TableDefs.Count - 1
  18.         DoCmd.RunSQL "INSERT INTO Db.TableDefs(i).Name IN todb SELECT Db.TableDefs(i).Name.* FROM fromdb  WHERE (Mid(Db.TableDefs(i).Name, 1, 4) <> 'MSys')"
  19.         Debug.Print Db.TableDefs(i).Name
  20.     Next
  21.  
  22. End Sub
  23.  
Dec 8 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi. You can't use the direct references to tabledef properties and VB variables in SQL statements - they mean nothing to the SQL interpreter. If the insert into approach is to work at all - and I'm not convinced it will - you will need to refer to the values of these items, not their names. I show part of the approach you will need to take below.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO " & Db.TableDefs(i).Name & " IN " & todb & " SELECT * FROM " & fromdb & ...
-Stewart
Dec 8 '08 #3

Expert 100+
P: 1,287
I'm not sure about the rest of your INSERT statement, but at first glance you have to change
INSERT INTO Db.TableDefs(i).Name IN
to
INSERT INTO " & Db.TableDefs(i).Name & " IN
Dec 8 '08 #4

Post your reply

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