General discussion

Locked

MS Access 97 VBA Question

By MRA2 ·
I import a table (called ALLDUE) from a disk several times a month. My VBA routine first renames the ALLDUE table adding the day number (ALDUE6 etc) and then imports a new ALLDUE table. I want to write a routine which will erase all tables ALLDUE1 to ALLDUE31. I tried, but when there is no table my routine crashes (unable to find table) Can you help???

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

MS Access 97 VBA Question

by SyscoKid In reply to MS Access 97 VBA Question

You need to get some kind of a counter in there, then DO WHILE counter > 0.

Sorry, I can't give you exact code (not a VBA guru, just a dabbler). :)

Collapse -

MS Access 97 VBA Question

by MRA2 In reply to MS Access 97 VBA Question

Needed more help than this. This routine won't work when you hit an used number

Collapse -

MS Access 97 VBA Question

by donq In reply to MS Access 97 VBA Question

It sounds as if you import records from the same table (AllDue) several times a month. If this is so and you allow Access to name your imports you wind up with what you have - multiple small tables with the same name + an integer concatenated on behind. BAD PRACTICE.....

Suggestion: Delete all records from inside one of the tables you need to discard and Change it's name to "PastDue". PastDue is now structured to match all imports from AllDue 100% - Now make PastDue your Target Table for an Append Query and use each AllDue (disk) as the record source (I assume the key fields are imported as well?). Just use your disk copies of AllDue as the record source and save everything to PastDue on the hard drive. Then once a month (or whenever maintenance dictates) write a Procedure to archive everything and clear the table, or write a Delete Query to clear all records. Click, click and you are done.

If that won't work write a Make Table query from the First "append" you do and name its data target PastDue. If you need more help (and you can read Access 2000) I would be more than happy to send you some code samples.

Hope this helps and good luck - Don Quigg, databyte@mindspring.com

Collapse -

MS Access 97 VBA Question

by MRA2 In reply to MS Access 97 VBA Question

This was a great help to me Thanks!

Collapse -

MS Access 97 VBA Question

by mbarker734 In reply to MS Access 97 VBA Question

This is as simple as I can make it.

Insert the following code where you want to perform the table deletion (I tested it using a Command Button).


Dim dbsName As Database
Dim tdfloop As TableDef

Set dbsName = OpenDatabase("mydatabase.mdb")

With dbsName
For Each tdfloop In .TableDefs
If tdfloop.Name = "Alldue1" Then DoCmd.DeleteObject acTable, tdfloop.Name
If tdfloop.Name = "Alldue2" Then DoCmd.DeleteObject acTable, tdfloop.Name
If tdfloop.Name = "Alldue3" Then DoCmd.DeleteObject acTable, tdfloop.Name
If tdfloop.Name = "Alldue15" Then DoCmd.DeleteObject acTable, tdfloop.Name
'Continue with one if statement for each table you want to delete
Next tdfloop
End With

Hope this helpsyou,

Mark

Collapse -

MS Access 97 VBA Question

by mbarker734 In reply to MS Access 97 VBA Question

Don't forget to change "mydatabase.mdb" to the name of your database.

Collapse -

MS Access 97 VBA Question

by MRA2 In reply to MS Access 97 VBA Question

Fantastic code. This really helped me!

Collapse -

MS Access 97 VBA Question

by MRA2 In reply to MS Access 97 VBA Question

This question was closed by the author

Back to Windows Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums