I am trying to import tables into a database. and i also want to create a query on the fly to delete the imported tables. don't need the tables, just need to extract the information from the tables. so i then created a delete query to delete the query and re-run it for the next table.

The Syntax error stated that I am missing ".*" from the expression. when I step through the code, it seems to work; until I try to actually import the tables. Help! :(

Public Function Imports()
'The Imports Function imports tables into the database.
    Dim qryDelete As QueryDef
    Dim rstImport As Recordset
    Dim db As Database
    Set db = CurrentDb()
    Set rstImport = db.OpenRecordset("tblFilePath")

    rstImport.MoveFirst
'This deletes the query and re-runs it for the next table
    Do Until rstImport.EOF()
        DoCmd.DeleteObject acQuery, "DeleteImports"
        Set qryDelete = CurrentDb.CreateQueryDef("DeleteImports")
        qryDelete.SQL = "DELETE " & rstImport.Fields(3).Value & " .* FROM " & rstImport.Fields(3).Value & ";"
        rstImport.MoveNext
        qryDelete.Close
        DoCmd.OpenQuery "DeleteImports", acViewNormal
    Loop


    rstImport.MoveFirst
    Do Until rstImport.EOF()
        DoCmd.TransferText [acImportFixed], rstImport.Fields(4).Value, rstImport.Fields(3).Value, rstImport.Fields(1).Value, "0"
        rstImport.MoveNext

    Loop
End Function

You might find this an easier way to go. It doesn't require using a saved QueryDef:

Public Function Imports()

    Dim rstImport As Recordset
    Set db = CurrentDb()
    Set rstImport = db.OpenRecordset("tblFilePath")
    rstImport.MoveFirst

    Dim strSQL As String
    strSQL = "DROP TABLE "

    Do Until rstImport.EOF()
        strSQL = strSQL & rstImport!TableName & ", "
        rstImport.MoveNext
    Loop

    ' Strip off last comma & space
    strSQL = Left$(strSQL, Len(strSQL) - 2)

    DoCmd.RunSQL (strSQL)

    ' Do your import...

End Function

If you use the DELETE data manipulation language statement, you will simply delete the records in each table, leaving their structure intact. If you really want to loose the tables, use the DROP TABLE data definition language statement. Also, there is no need for a QueryDef. Just build your SQL string by looping through your table names, then execute a DELETE query using DoCmd.RunSQL.

Bob

thanks Bob! Forgive me for sounding like a complete putz (I've been doing VBA for a little over a month now), but... I tried to compile this and I got this error: Syntax Error in Drop Table. Where do I put the Delete statement.

Public Function Imports()

    Dim rstImport As Recordset
    Dim db As Database
    Set db = CurrentDb()
    Set rstImport = db.OpenRecordset("tblFilePath")
    rstImport.MoveFirst

    Dim strSQL As String
    strSQL = "DROP TABLE "

    Do Until rstImport.EOF()
        strSQL = strSQL & rstImport.Fields(3).Value & ", "
        rstImport.MoveNext
    Loop

    ' Strip off last comma & space
    strSQL = Left$(strSQL, Len(strSQL) - 2)

    DoCmd.RunSQL (strSQL) <---- THIS IS WHERE I GOT THE ERROR

    ' Do your import...

Couple of things to check.

First, does a table actually exist in your db for each of the table names you have listed in tblFilePath? You can't drop a table that doesn't exist (code should actually check that condition first).

Second, does rstImport.Fields(3).Value refer to the field in your recordset that holds the table names. Remember, the Fields collection is 0 based, so Field(3) is the 4th field in your table. That's why I prefer rstImport!TableName syntax. Your field reference is self-documenting. Your TransferText arguments seem to be:

SpecificationName = rstImport.Fields(4).Value
TableName = rstImport.Fields(3).Value
FileName = rstImport.Fields(1).Value
HasFieldNames = False

Third, and most importantly, add the line:

Debug.Print strSQL

just before the line:

DoCmd.RunSQL (strSQL).

When using embedded SQL, its alway a good idea to check your SQL statement in the Debug window (open with Ctrl-G) before executing it. I should have left that line in.

When I run my code, this is the valid SQL statement is builds:

DROP TABLE Table1, Table2, Table3, Table4, Table5, Table6

DoCmd.RunSQL executes without errors, and drops ("deletes") all 6 tables from my db.

In other words, the code you posted back works for me if I change:

rstImport.Fields(3).Value

to:

rstImport!TableName

which is simply a function of how I set up my tables.

Anyway, let me know what SQL statement Debug.Print gives you.

Oh, again, if you want to drop ("delete") the tables from your db, you don't use DELETE to do that, you use DROP TABLE. DELETE only deletes records from tables, not the tables themselves.

HTH,

Bob

Bob,

Thanks for the help. To answer your questions.

1. I have actual tables that I programmatically import into the database.
2. However, I created a table with all the table names so the query can be run from one table. So the table, tblfilename actually lists every table, and that's how i defined my recordset rstImports.

i'm thinking that this is the reason why at this line (rstImport!TableName) I get the runtime Error (3265), Item not Found in Collection

and now that i've gotten to the point where i've dropped the tables, I have a bunch of white space. So I need to automate the compacting process. I know this topic has been done to death, but with the various utilities i've seen over the net, i'm not sure which one is good to use...and how (exactly to the letter) to use it.