[RESOLVED] [Access 2003] Let Access Add Primary Key

[agmorgan]

I am trying to script the Import Spreadsheet Wizard.
I used the tip here http://www.microsoft.com/technet/scr...7/hey0404.mspx to create an access database from an Excel workbook.

However, this misses out the crucial step at the end where the wizard says "Let Access Add Primary Key"

How do I add the primary key?
At the moment I have a vbs file that looks like this.

Option Explicit

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim Path
Path = fso.GetParentFolderName(WScript.ScriptFullName)
'*
Dim cMDB
Dim cXLS
cMDB = Path & "\db1.mdb"
cXLS = Path & "\myfile.xls"
'*
Dim objACC
Set objACC = CreateObject("Access.Application")

objACC.NewCurrentDatabase cMDB
objACC.DoCMD.TransferSpreadsheet 0, 8, "java", cXLS, False, "java!"
objACC.DoCMD.TransferSpreadsheet 0, 8, "visualb", cXLS, False, "visualb!"

objACC.closeCurrentDatabase
Set objACC = Nothing

[agmorgan]

Didn't realise that you could add SQL in the VBScript file.

Just did added the following after the TransferSpreadsheet commands

objACC.DoCMD.RunSQL "ALTER TABLE java ADD COLUMN ID COUNTER PRIMARY KEY"
objACC.DoCMD.RunSQL "ALTER TABLE visualb ADD COLUMN ID COUNTER PRIMARY KEY"