Transfer (a lot of) data from SQLServer 2K to Access 2K
-
Hello all,
I have a SQL database with a simple table A (see design):
nr int IDENTITY
streamID int
datestmp datetime
volume real
typeID char(1)
This table consists of 80 million records.
I have created a DTS in which 12 months of a chosen year are transferred to an Access database; for every month I created a view, every view is linked to table B (which has the same design as table A) in the Access mdb.
(I first tried to transfer one year in one go, but that never managed to successfully finish the job).
The SQLServer data and Access data are on the same drive.
The transfer takes about half an hour per month, so in total about 6 hours. As one year typically has about 40 million records, and each record is 21 bytes, so the transferrate is about 38 kB /s. I can imagine there are faster ways.
Does anyone have experience and give me a clue?
tia
Hans
-
How long does it take to just run the select without transfering to access?
-
If I execute the select-statement (from Query Analyser) it takes about 2 minutes per month. Every month consists of the following statement (see example for Januari 2005):
SELECT nr, streamID, datestmp, volume, typeID
FROM dbo.A
WHERE (datestmp>= CONVERT(DATETIME, '2005-01-01 00:00:00', 102) AND datestmp< CONVERT(DATETIME, '2005-02-01 00:00:00', 102))
This statement is saved in a view. I have 12 views, these are (via a connection to Access) saved in a DTS.
If I execute the transfer for 1 month from within the design of the DTS, it takes about half an hour.
-
That's only 70 mb of data to transfer... it shouldn't be lightning fast but it shouldn't take 30 minutes.
Is the Access db on the same machine as the sql server (can the network be slowing things down)?
Have you tried creating a view in sql server, then import data from the access side using that view (to see if it's faster)?
-
I tried to create a view but the DTS returns 'data type mismatch in criteria expression'. The view it created is as follows:
select `A_x`.`datestmp`, `A_x`.`typeID`, `A_x`.`streamID`, `A_x`.`nr`, `A_x`.`volume`
from `A_x`
where `A_x`.`datestmp`>='1/1/2005' AND
`A_x`.`datestmp`<='31/1/2005'
A_x is the name of the table in the Access mdb.
I didn't manage to get the format of the criteria right, so I created a query in the Access mdb instead, and buitl a DTS to import that query. This worked, and took less than 2 minutes!
-
Let me restate what you are doing :
You have a table on sql server with 80M + records that you need to transfer to access.
You can simply dts the data out sql server to access.
Or create a normal view (permanant) in sql server from which access can pump the data from.
Is this what you are trying to do? Or do you have more steps involved?
-
Datatransfer: from sql server to Access
I have a set of 12 views, one for each month.
I created a DTS in which all the 12 views are used to pump data over to the Access mdb.
The Access mdb is on the same machine, same hard disk, as the sql server data.
-
Why do you want to transfer this data to Access, particularly as it is on the same server as SQL?
If you want to use the Access forms and reports against the data, it could be better to set up linked tables in Access that point to SQL Server. This is relatively easy to do, and saves the hassle of transferring data, holding multiple copies, etc.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
The data have to be supplied to users who do not have Sqlserver on their own system, and have no access to our sqlserver.
We're only talking raw data here, no forms. The users are send an Access mdb and are free to do their own querying on it.
-
SQL is poorer than some other DBMSs in recognising dates in character strings. If dates are in YYYY/MM/DD format (you can use other delimiters in place of the /) your query should work - i.e.
where `A_x`.`datestmp`>='2005/1/1' AND
`A_x`.`datestmp`<='2005/1/31'
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Yes, this works a lot faster. I still don't know why DTS is so slow, but I think I'll opt for the import from Access instead of the export from SQLServer.
Thanks for all the help.
Hans
-
Hmm, solved one problem but got another one instead: when I checked the entries for the year 2005 I saw there are records with datestamps for July, August etc. As the database consists of realtime data, and I don't posess a timemachine
there must be something not quite right.It turns out that the import from Access swaps the day and the month, so that I get entries 8-1-2005 (ie. 8 January 2005) stored in Access as 1-8-2005 (Access thinks this is 1 August 2005).
Any idea anyone?
-
Forget the last entry, it turns out the data are not clean! My mistake, should have checked this first.
Consider this thread closed please.
-
Happy to hear that... what's the final transfer time after the modifications?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply