How do I copy table structure (w/o data) using VB in MS-Access?

G

Guest

I'm trying to create a new database that has auto-numbered records,
restarting the record at the beginning of each year. I figure I can do it
easily by having a function that will take my "last-year's" table, rename it
to "last year", and then copy "last year's" table structure but NOT the data.
I'm sure there's a way to do it in VB, or in SQL, but I just can't find
anything on how to do it. A make-table query copies the records in the
table, as does a "CopyObject" macro or VB command.
 
G

Guest

At the database level - showing your tables - click on your table and then
click on the tool bar icon for copy. Then click on the one for paste and
select structure only.
 
G

Guest

Try this

docmd.TransferDatabase acImport,"Microsoft Access" ,"Location and name of
the mdb",acTable,"old tabke name","new tble name",true

You can import from the current mdb
The true stand for structure only
 
G

Guest

The way the records are tracked in our system is basically yy-<autonum>
So the first job of this year was 05-001, the second was 05-002, and so on.
I need something that will automatically increment the second number and
restart at the new year, so that the first job of 2006 will be 06-001. Since
there will potentially be several people creating new jobs, I can't have it
manually entered, because they may not know what job number is the next in
line.
 
G

Guest

I'm trying to make it idiot-proof, though - most of the people that'll be
using it aren't real happy about using computers in general, and asking them
to manually change the name of one, and copy the structure out, and have the
new (empty) db take the name of the old one (so that no forms, queries or
reports need changing) would be a bit too much to ask them, really.
 
G

Guest

I thought of an addendum: if there's a way to have the field change with the
year in the same table, and re-set the job number to <newyear>-001 at the
beginning of each year, even if it required a single button-push or
something, I'd be ecstatic.

If I could have, say, record #53 read 05-053 and record #54 read 06-001
*automatically*, that'd be the best situation. I just haven't found a way
to do that; every time I try I get a coding error.
 
L

Lynn Trapp

Kim,
You can still track the records that way and not have to create multiple
tables. Do something like this in the BeforeUpdate event of your data entry
form:

If IsNull(Me.DocumentYear) Then
Me.DocumentYear = Format(Year(Date), "yy")
End If
If IsNull(Me.DocumentNumber) Then
Me.DocumentNumber = DMax("DocumentNumber", "LegalDocuments", "DocumentYear
= " & Me.DocumentYear) + 1
End If

Then you can simply concatenate the 2 fields (DocumentYear and
DocumentNumber) to display the way you are accustomed to.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
G

Guest

Yes it would, just specify the location to your current mdb.
But it look like you dont need that any more
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LINK MS ACCESS TABLE TO EXISTING FORM 3
copy table structure 10
how do i append a query? 1
Table structure 2
Export Access table structure in a Word table 8
Access Access 2010 import/append data into table w/o overwriting existing records 0
Copy table structure in Access 2000 4
Trying to Copy Structure - Error msg for Table Name 2

Top