Set Primary Key automatically

[HolgerP]

Guys,

I have a database which imports a spreadsheet into Access. The table is deleted and created again every time the file gets imported. That means my Primary key dissappears. Is there a way to automatically set the primary keyx through an automated process? Thanks for all ideas...

 

[Joe4]

When importing a file, if you click on the "Advanced" tab, you'll see an import specification. You can save your import specification to use like a template to import future files.

I have done something like this, along with macros, to delete ONLY the data in my tables (not the structure), then import the new data.

You would do this in the macro by doing the following:

Line 1:
Action: RUNSQL
SQL Statement: DELETE [Table1].* FROM [Table1];

Line 2:
Action: TransferText
TransferType: Import Delimited or Import Fixed Width
Specification Name: Import specification name (can select from combo box)
Table Name: Table1
File Name: name of file to import

 

[HolgerP]

jmiskey,

Great stuff. It worked. The Primary key remains since only the content will be deleted. The only thing I needed to do is to disable the appearing message, to confirm that I want to delete, otherwise my Macro would stop every time.
I came across this MrExcel a while ago and I would not know what to do without it. You guys are the greatest !!!!!!

Keep on Exceling and Accessing.....
Thanks, Holger

 

[Joe4]

HolgerP,

It sounds like you may have already figured it out, but in case you hadn't, you can add a few more lines to the macro to suppress the Warnings.

Insert a row at the top of the macro and select the "SetWarnings" action, and set it to "No". Likewise, add a line to the bottom of the macro setting the "SetWarnings" back to "Yes".

 

[Philem]

Holger,

Put a line at the BEGINNING of your macro with SetWarnings=False, and a line at the END with SetWarnings=True. That should do the trick.

HTH

P

 

[HolgerP]

Already did that. But since I have the both of you online, I have trouble with a query, which adds an Auto Number to the table.

The spreadsheets I import don't have numbers but I would like to add an Auto-Number to each table through an update query. For some reason it won't add the new field. Any idea?

 

[Joe4]

I am not certain, but I don't believe you can update an AutoNumber field through a query (or at all, for that matter).

If possible, you want want to look at using another kind of identifier (I don't use Autonumber at all). If that is not possible, you may want to look at creating a "Pseudo" autonumber, and write some VBA code that will look at your table, find the highest number, and then start incrementing from there.