Hi,

I have an access database that stores login and logout times of employees in
my company.

For a particular day a table is generated and all logins/logouts of that day
is stored in that table. My table naming convention is DT20030210. This
would be for February 10th, 2003.

What I need to do now is to port all this data into a single SQL Server
table. This will be used for analysis purposes. But the MS Access system
would still be maintained. So what I plan to do is to run a job at 12.01 AM
everyday to transfer data from MS Access to SQL Server 2000.

But the problem I am facing is I do not know how to dynamically determine
the table name based on the date? Is this possible via DTS? If not via DTS
can someone suggest how I can go about transferring the data to SQL Server.

Thanks in advance.

Regards,
Karthik.

Hi
You can change the source table by using the techniques described in the
following:

http://www.sqldts.com/default.aspx?205
and/or
http://www.sqldts.com/default.aspx?213

John

"Karthik" <karthik (AT) maximizelearning (DOT) com> wrote

Rather than using a dts package you could just run a sql
job that executed a sql statement if you just create a
linked sevrer to your access db.


SET @TableName = 'DT' +
LTRIM(RTRIM(CONVERT(CHAR,DATEPART
(year,getdate())))) +
REPLICATE("0",2- LEN(LTRIM(RTRIM(CONVERT
(CHAR,DATEPART(month,getdate()))))) +
LTRIM(RTRIM(CONVERT(CHAR,DATEPART
(month,getdate())))) +
REPLICATE("0",2- LEN(LTRIM(RTRIM(CONVERT
(CHAR,DATEPART(day,getdate()))))) +
LTRIM(RTRIM(CONVERT(CHAR,DATEPART
(day,getdate()))))

INSERT INTO SQLTable
( fields )
SELECT
( values )
FROM AccessDb..@TableName

You could accomplish the same thing in your DTS package.