output sql server data to .mdb file

[Carl Henthorn]

I have a sql DTS job that is supposed to export to a single table in an
access 2003 .mdb file. I used sql syntax to drop and recreate the table for
each run, but the .mdb file only seems to grow in size. the data export is
working fine.

Is there a special Access syntax that I would use to access the .mdb file
and drop/recreate the table before importing data so I always get a blank
table to import into?

Thanks in advance!

 

[Douglas J. Steele]

Access doesn't release unused space until you Compact the database. That's
why your database is constantly increasing in size.

 

[Carl Henthorn]

thank you for that! I am completely new to Access. Are you saying then that
the syntax is correct?
Drop table <table name>

Create table <table name> (
`col1' varchar(20),
`col2` int )

Thanks!!

 

[Douglas J. Steele]

Personally, I'd just delete the data from the table and append to it, rather
than deleting it and recreating it.

If you are using the DDL, you don't need quotes around the field names
(unless they have embedded blanks, which isn't recommended). Use Text(20)
rather than VarChar(20), and be aware that an Integer in Access is only a 2
byte field (capable of holding values from -32,768 to 32,767). The Long
Integer (specified as Long) is the equivalent of the SQL Server Int data
type.