Because this is a SELECT ... INTO statement, you will need to do some housekeeping to run it multiple times. The first run creates the table "nFile" and subsequent runs will also attempt to create it but it already exists so you will get a dialog that says "Error 3010 - Table 'nFile' already Exists". When you run something directly from Access it deletes the current target table and creates a new one; running from VB does not automatically do the delete. You just get the error and no new table is created. You will therefore need to get rid of the old copy before running the code.
Here's an example of the code that you will need to do that in VB.
Dim db As DAO.DataBase
Dim tbl As TableDef
Set db = DAO.DBEngine(0).OpenDatabase("Path\MDBFile"
' This loop just removes 'nFile' if it exists.
For Each tbl in db.TableDefs
If tbl.Name = "nFile" Then
db.Execute "Drop Table nFile;"
Exit For
End If
Next tbl
' Now you are ready to run your SQL
db.Execute "Select *, (tDollars * sTax) as nAmt INTO nFile FROM dFile "
If you want to run ADO (the above example uses DAO) you will need to use a connection rather than a database.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.