Solved For each..... insert into (1 Viewer)

[zazar]

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.

 

[MajP]

There may not be any need to loop a recordset. You may be able to do a Insert Into Select. See discussions

SQL INSERT INTO SELECT Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[zazar]

Thanks for the link. I'll try

 

[zazar]

I had this wich work great :

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 ?

 

[theDBguy]

Hi. Maybe try:

strSql = "INSERT INTO MainTable SELECT * FROM " & strTable

 

[zazar]

Hi. Maybe try:

strSql = "INSERT INTO MainTable SELECT * FROM " & strTable

It works great...

Thank you all guys...

 

[jdraw]

Or, since Maintable is a variable name containing "OPERATION", and strTable contains "TEMPORJ".
try

strSql = "INSERT INTO '" & MainTable  & "'  SELECT * FROM '" & strTable & "';"

 

[theDBguy]

It works great...

Thank you all guys...

Hi. Glad to hear it worked for you. Good luck with your project.

 

[zazar]

Or, since Maintable is a variable name containing "OPERATION", and strTable contains "TEMPORJ".
try

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

 

[theDBguy]

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:

 strSql = "INSERT INTO MainTable SELECT * FROM " & strTable & " WHERE ID_Stock IS NOT NULL"

Do you see a pattern now?

 

[zazar]

Try it this way:

 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.

 

[theDBguy]

Yes. I'm seeing how it works
Thanks again.

Glad we could assist. Cheers!