Compact & Repair

[shairal]

I am sure I’m going about this in the most difficult way possible but … I’m
wondering if there is a way to add Compact & Repair in a macro or VBA code (I
have very little experience in VBA)?

I need the AutoNumber in my Access table to reset each time a user runs a
macro I created.

Currently I have a macro that runs an Append query to add the data to the
table, an Update query which sends the data to my SQL table, and a Delete
query that removes all the existing data from my table. However I need the
AutoNumber to reset before the next file is loaded. I use the AutoNumber as
a line number in my SQL table. I have Access set to compact on close, but
if the end user doesn’t close before loading the next file, the AutoNumber
continues with the last number it created.

I'm using Access '07, but saving as an .mdb since some of my users are still
on '02 and '03.

Any suggestions would be appreciated!

 

[Clifford Bass]

Hi Shairal,

See <http://support.microsoft.com/default.aspx/kb/287756> for a couple
of ways to do that.

Clifford Bass

 

[shairal]

Thanks Clifford for your response. I am very limited when it comes to VBA -
I don't understand the line:
'Set connection and catalog to current database.

I've searched the web and have found very limited help. Any advise??

Thanks again!!
Shairal

 

[shairal]

Thanks!! I got it figured out, and it's working (yippee!)

 

[Clifford Bass]

Hi Shairal,

Hurray! You are welcome.

Clifford Bass