473,325 Members | 2,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Macro to delete table

I have a macro set up to delete a table and then it imports an up-to-date
copy of the table. Every once in a while the table gets deleted but the new
one isn't imported, I assume the user is closing the database before the
macro finishes running. The next time the macro runs it comes up with an
error becuase the delete table part of the macro can't find the table. Is
there any way to have the macro check to see if the table is there and if
not skip the delete piece?
Apr 13 '07 #1
2 13384
Hi Scott,

I think you may have to venture into the world of VBA ... instead of trying
to accomplish this with macros.
It's an evoutionary thing ... as your skills progress, so will your
expectations ... so you will likely end up having to learn some VBA. :)

You could use this function (courtesy Chuck Grimsby) which is quite simple:
================================================== =
Function DropTheTable(strTableName As String, _
Optional bolShowErrors As Boolean = False) _
As Boolean
Dim myDB As DAO.Database
Set myDB = CurrentDb
On Error Resume Next
myDB.Execute "DROP TABLE [" & strTableName & "]", dbFailOnError
If Err.Number <0 Then
If bolShowErrors = True Then MsgBox (Err.Description)
DropTheTable = False
Else
DropTheTable = True
myDB.Tabledefs.Refresh 'This was added by me.
End If
Set myDB = Nothing
End Function

I actually have some code that automates the whole process, from
start-to-finish, for a fixed-width text-import (if that is what you are
doing)
Let me know if you're interested in seeing it.

"Scott" <sc**********@ns.sympatico.cawrote in message
news:do********************@ursa-nb00s0.nbnet.nb.ca...
>I have a macro set up to delete a table and then it imports an up-to-date
copy of the table. Every once in a while the table gets deleted but the
new one isn't imported, I assume the user is closing the database before
the macro finishes running. The next time the macro runs it comes up with
an error becuase the delete table part of the macro can't find the table.
Is there any way to have the macro check to see if the table is there and
if not skip the delete piece?

Apr 14 '07 #2

Dropping a table, just to re-create it is a *very* bad idea, abet a
rather common practice that can (will) lead to the corruption of your
database.

Please take the time to change your practice to "Clearing" the table
(via a delete query), then importing the new data. If this is
something you do frequently, you may also want to think about creating
a "temp database" to hold this "temp data", which can then just be
deleted and re-linked as needed. (If you include a date/time in the
filename, you may also gain data recovery capabilities if that would
be an enhancement to your system.)
Scott wrote:
I have a macro set up to delete a table and then it imports an up-to-date
copy of the table. Every once in a while the table gets deleted but the new
one isn't imported, I assume the user is closing the database before the
macro finishes running. The next time the macro runs it comes up with an
error becuase the delete table part of the macro can't find the table. Is
there any way to have the macro check to see if the table is there and if
not skip the delete piece?
Apr 15 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jason L. | last post by:
Here's the scenario. Using a campaigning applicaion for an eNewsletter. It writes to an Access table. I have an ASP web page that writes to another table. I chained 3 Access queries together into...
6
by: steve lord | last post by:
Greetings all, I have a macro that should add a column to a table if the column doesn't already exist. Using the macro condition, how can I test for whether a specific column name in a specific...
3
by: john | last post by:
In a macro I use TextTransfer to import a textfile with an importspecification. Is it possible to make sure the old imported table gets overwritten by this action? Because now, the import doesn't...
8
by: Net | last post by:
Hi Please help. I have a database which requires a message to come up when certain part numbers are added to it. I have solved part of this by using a conditional macro eg = 54125 and using a msg...
5
by: solargovind | last post by:
Hi, In VBA code, How can i run Sql query..? I need to delete a record by the below query... Docmd.runsql(Delete * from Payment_detail_Table where Payment_id=(Select Max(Payment_id) from...
0
by: =?Utf-8?B?TGV0emRvXzF0?= | last post by:
I'd like to create a Macro that will sort some raw data, apprx 20k lines, remove some lines based upon a condition in a certain column. Then copy this data into a new spreadsheet and sort the ...
0
by: camaro71 | last post by:
Hi there, I really hope someone can help with this one. I have a simple recorded macro (code below) which I'm trying to modify. It's for use in MS Word 2003 tables: With the cursor in front of a...
0
by: acarrazco | last post by:
Hello, I am totaly new to VBA and I'm trying to modify a macro that was given to me but it doesn't seem to be working. I'm trying to extract data from three excel spreadsheets, put it into a combined...
1
by: bytes access nubie | last post by:
Hello. I'm trying to create one macro in Access 2003 that will delete a field (called ID) in a table. I then need a separate macro that will *add* a field back in called ID w/type of autonumber. I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.