AutoNumber to field in table from MakeTable Query via VBA? 1

[monbois]

The advice provided in the following thread (

http://www.tek-tips.com/viewthread.cfm?qid=1070674&page=2)

is tantilizing, but imcomplete for someone like me who's never done it before.

How do I add an AutoNumber field to a table made from a MakeTable Query? The resulting table is called 'mktbl_reportspage' and in the MakeTable Query I've added a null field titled 'rp_id'. Now, what VBA code can I write to turn that field and populate it with new values each time I make the table? I've tried manipulating the elusive coding provided in the example above, but with no luck; my skills just aren't that advanced.

Thank you so much if you can help me.

Frank

 

[Trevil]

In case you don't get the real answer, you could do what you want by running two queries. The first would be:
'Create Table mktbl_reportspage (fld1 Text, Ctr Counter);'

Then change your existing query to an append query. If I stumble across the code to allow you to append a field with the AutoNumber, I'll send it.

Code: Where the vision is often rudely introduced to reality!

 

[monbois]

Thanks. I'm already ahead of you; I've changed the Make-Table Query to an Append Query, and in the object table I've inserted an AutoNumber field.

For various reasons, I was hoping to avoid this because now the autonumber will grow and grow and grow to the point where even though there may be only 300 records in the table, the 1st AutoNumber number will be very high every time it's updated.

Even code to resent the AutoNumber field to start at '1' would be nice, but I can't find that, either. If you've got anything like that handy, please let me know.

Thanks,

Frank

 

[Trevil]

You could:
1. delete the existing table (if it exists)
2. Run make table query.
3. Run Append Query

Or:
1. delete the existing table (if it exists)
2. Create table via code
3. Run Append Query

Sample of ADO code to create AutoNumber field is:

http://msdn.microsoft.com/library/d...g/html/deovrcreatingmodifyingaccesstables.asp

Sample to reset AutoNumber field:

http://support.microsoft.com/default.aspx?scid=kb;en-us;287756

Good Luck!



Code: Where the vision is often rudely introduced to reality!

 

[monbois]

Wow! I never thought of creating a new table via code. I'll have to look into that, though I'm not sure I have the time to do that for this project I'm working on right now. I've never done that before and I'd have to research on how to format all the fields. But even if I don't don't do it with this particular databse, I'll definitely try it before long.

Thanks again!

Frank