Dynamically Creating MS Access Table

[SehDavies]

Hi,

I am trying to create a series of MS access tables from within a SQL 2000 DTS package. The names of the tables reflect the date range of the data within them. Creating the "Create table ddmmyy ...." statement isn't a problem (full code below), but doesn't run as it creates a Jet error - "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Does anyone know if what I'm trying to do is possible.

Before anyone suggests it, yes I have posted this on SQL server forums.

declare @TheName char (8)
,@Cmd char (500)

select @TheName = (select max(LastRunDate) from NSP_Analysis_Dates)

select @Cmd = 'CREATE TABLE `NSP_Analysis_' + @TheName + '` (`TYPE` VarChar (200) NULL, `VENDOR_NO` VarChar (10) NULL, `VENDOR_NAME` VarChar (50) NULL, `AMOUNT_CLAIMED` Currency NULL, `REGION` VarChar (100) NULL, `CLUSTER` VarChar (100) NULL, `PLANT_CODE` VarChar (15) NULL, `PLANT_NAME` VarChar (50) NULL, `DATE_OF_SERVICE` DateTime NULL, `STATUS` VarChar (200) NULL, `TICKET_NO` VarChar (20) NULL, `NOTES` VarChar (255) NULL, `DATE_ENTERED` DateTime NULL, `AuthorisedByName` VarChar (50) NULL)'
sp_executesql @Cmd

 

[Dennisk]

you sql has to match up with the version in access and not sql so using SELECT @cmd means nothing to access. Why not create the tables on your sql server then DTS them to Access?