Solved For each..... insert into

zazar

New member
Local time
Today, 03:24
Joined
Jul 8, 2020
Messages
14
hello,
I have this simple code to import data from an excel file to a temporary table

Private Sub ImportXls_Click() Dim Filepath As String Dim strTable As String Filepath = Me.fpath.Value strTable= "TemporJ" DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, Filepath, True

That works great. Now I need an query wich insert each row on that temporary table to the actual table. I read that it would be an "For each" query and I'm not good enough to write it by my own yet.
Actual table and temporary table have same field names "amount" (integer type) ,"TransDate" (date type), "reference" (string type).

Any help would be really appreciate.
 
There may not be any need to loop a recordset. You may be able to do a Insert Into Select. See discussions
 
Thanks for the link. I'll try
 
I had this wich work great :
Code:
Private Sub ImportXls_Click()
Dim Filepath As String
Dim strTable As String
Dim MainTable As String
Dim strSql as String
Dim base As DAO.database

Filepath = Me.fpath.Value
MainTable = "Operation"
strTable= "TemporJ"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, Filepath, True

    Set base = CurrentDb
        strSql = "INSERT INTO MainTable SELECT * FROM TemporJ"
    base.Execute strSql, dbFailOnError
    base.Close
    Set base = Nothing
DoCmd.DeleteObject acTable, strSheet
MsgBox "Data have been imported.", vbInformation,
End Sub

BUT the problem is that this line only works with the table name
strSql = "INSERT INTO MainTable SELECT * FROM TemporJ"

When I put "FROM strTable" I get the error 3078 (table doesn't exist and something).
How could I fix that because the table name should be variable ?
 
Hi. Maybe try:
Code:
strSql = "INSERT INTO MainTable SELECT * FROM " & strTable
 
Or, since Maintable is a variable name containing "OPERATION", and strTable contains "TEMPORJ".
try
Code:
strSql = "INSERT INTO '" & MainTable  & "'  SELECT * FROM '" & strTable & "';"
 
Or, since Maintable is a variable name containing "OPERATION", and strTable contains "TEMPORJ".
try
Code:
strSql = "INSERT INTO '" & MainTable  & "'  SELECT * FROM '" & strTable & "';"
Hi, your code gives a error 3450 "Syntax error in query. Incomplete query clause"

I know the post is solve but now I found that i actualy need to put a where clause...
I try this :
strSql = "INSERT INTO MainTable SELECT * FROM " & strTable WHERE ID_Stock IS NOT NULL
but i'm getting error
 
Hi, your code gives a error 3450 "Syntax error in query. Incomplete query clause"

I know the post is solve but now I found that i actualy need to put a where clause...
I try this :
strSql = "INSERT INTO MainTable SELECT * FROM " & strTable WHERE ID_Stock IS NOT NULL
but i'm getting error
Try it this way:
Code:
 strSql = "INSERT INTO MainTable SELECT * FROM " & strTable & " WHERE ID_Stock IS NOT NULL"
Do you see a pattern now?
 
Try it this way:
Code:
 strSql = "INSERT INTO MainTable SELECT * FROM " & strTable & " WHERE ID_Stock IS NOT NULL"
Do you see a pattern now?
Yes. I'm seeing how it works
Thanks again.
 

Users who are viewing this thread

Back
Top Bottom