Create table with autonumber field through vb

[Tadynn]

Hi all,

I want to create a table that will have a structure (field names), but will contain no data. One of the fields I need to be an autonumber field formatted as "00000". Below is a list of the fields that the table needs to contain:

TABLENAME

tmp_ManifestDetail


FIELDNAME DATATYPE FORMAT

ManifestNo Text "\M000000"
LineNo autonumber "00000"
ProductCode Number
Qty Number
DespatchToday Text
DespatchDate Date "dd/mm/yyyy"


The script that builds the table I am going to attach to a button on a form.
This table is going to be used as a temporary table and once all the information is entered. All the data is going to be appended to another table "tbl_ManifestDetail".
The next time the user opens the form and hits the button the table will be overwritten again, kind of like running a make-table query.

I figured that by using a temp table and recreating the table everytime a user creates a new manifest, I could reset the autonumber count to 00001 each time.

The version of access that I am using is 2k.

Can anyone help?


Rgrds, tadynn

 

[Tadynn]

Hi,

Disregard, I've figured it out

 

[ajaeger]

How did you do it?

I have the following that works, but I really need ID to be an autonumber. What is the syntax for an autonumber?

strSQL = "Create table tblTest (ID number, EventID number)"

Thanks.


Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com