Import Data From MS access Databases

Last Post 29 Dec 2007 03:44 AM by snoopsterg. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
snoopsterg
New Member
New Member

--
27 Dec 2007 12:59 PM
Hi,
Merry Christmas and a Happy New Year To all.

I know its sad to be working over the festive period, But i am really desperate to beat this problem i am having with Data that i cannot figure out.

I have the a Data Capture system that dumps Data into the following MS access Databases.

\\Prodigy2\TascompLtd\Prodigy\Data\Batches\CoilData.Mdb
This Database has a table called Batches that is required to be imported as CoilData

\\Prodigy2\TascompLtd\Prodigy\Data\Batches\ShiftData.mdb
This Database has a table called Batches that is required to be imported as ShiftData

\\Prodigy2\TascompLtd\Prodigy\Data\DownTime\01122007.mdb
This is a Problem as a new Database is created at Midnight every evening with the new Date as the database Name.

I have created my Database in SQl 2000 and called it Prodigy, It has tables called tblCoilData, tblShifData and tblDowntime.

There are no relationships on the original Access database's so i have created the relationship a follows:
(I may need these correcting i am not sure if i am correct)

ShifData ---> CoilData is 1 ---> Many
A shift can produce Many Coils

This is the one i am not sure of because a coil can have many downtimes, but a downtime is not necersary to be linked to a coil as it may break down in between, at the moment it is:

CoilData -----> DownTime is 1 -----> Many

This is all Done manually when i arrive in in the morning, but would like to Automat the process.

i would be most grateful for any help as to how to get the data into SQL with a script usinig Query Analyser or a better way if possible

Regards


Steve Dyson
snoopsterg
New Member
New Member

--
29 Dec 2007 03:44 AM
rm,

Thanks for that, i have been going through the SQL CBT Nuggets and come accross Linked Servers, But cannot figure out how connect to the access database, do you have any resources that will talk me through connecting to the Access database correctly

Sorry if i seem to need my hand holding, but connecting to non SQL servers is all new to me


Regards

Steve
SQLUSA
New Member
New Member

--
30 Dec 2007 12:36 AM
>do you have any resources that will talk me through connecting to the Access database correctly

Certainly, plenty of consultants available, although this may not be a big enough engagement for them.

You can open a support case with Microsoft.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/highperformance2005/ The Best SQL Server 2005 Training in the World!



Acceptable Use Policy
---