creating a viewable query from visual basic in access 1

[R9772]

can anyone help me create a query through visual basic code that i can manipulate from visual basic in Access?

 

[Golom]

Yes ... but more information is needed to be able to provide a more useful answer. Query of what? How do you want to "manipulate" it?

 

[R9772]

i have an existing table that i want to run calculations off of. my current code looks like this:

myStr = "select *, (tDollars * sTax) as nAmt, into nFile from dFile "

i would like to be able to open and close this from visual basic

 

[Golom]

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&quot

' 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.

 

[R9772]

thanks, that worked perfectly.