Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 23rd, 2004, 08:25 AM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access VBA Delete Query Issue...

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

Reply With Quote
  #2 (permalink)  
Old June 23rd, 2004, 02:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old June 24th, 2004, 08:51 AM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old June 24th, 2004, 01:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


Reply With Quote
  #5 (permalink)  
Old June 24th, 2004, 03:57 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use VBA to Read/Write a Access Query Gino Liu Access VBA 2 January 7th, 2012 04:03 AM
Access and SQL query Issue ayazhoda Access VBA 0 May 16th, 2008 07:54 AM
How to set properties of query in Access VBA LittleCity Access VBA 2 September 27th, 2006 08:42 AM
Passing Query from Access to MySAL using VBA stephej Access 3 June 20th, 2006 06:56 AM
Delete Query in Access Data Project (ADP) Pavesa Access 1 February 18th, 2005 08:38 AM



All times are GMT -4. The time now is 01:43 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.