 |
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

June 23rd, 2004, 08:25 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

June 23rd, 2004, 02:24 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|

June 24th, 2004, 08:51 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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...
|

June 24th, 2004, 01:26 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|

June 24th, 2004, 03:57 PM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
 |