Slow loading data into SQL table through Access

[BSman]

I have an application using Access 2000 and SQL Server 2000 for the data. There is data that is currently loaded from a text file into an interim table in the Access front end. Some updates are performed on the loaded data, then the result is appended to the working table in the SQL database. The initial loading from text file runs fairly quickly.

I would rather place the interim table in the SQL database and load it, make the updates in SQL (probably through a pass through query), and then, probably through another pass through query, append them to the working table. By working this way it avoids the need to peridocially compact the Access front end.

However, every time I've tried the loading into the interim table in SQL it takes a very long time, probably at least 5-10 times as much time as when the table is in Access.

The version of the interim table includes an added identity (autonumber) type column so the connection is faster and because there is no single column that could be set as the primary key.

Does anybody have an idea how to get the loading to work at about the same speed when the interim table is in SQL versus when it's in Access?

Bob

 

[AlexCuse]

Hiya Bob,

I am not sure how comfortable you are with SQL, but I have had great success for tasks like this using simple(r) DTS packages, and then calling them from the command line through Access. You may want to look into this?

How many records are you talking, and how long is it taking for you?

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BSman]

Alex,

I'm comfortable with Access, SQL, and SQL SErver. I've used the SQL DTS package, but part of the problem may be referencing the location of the source file (which is small, perhaps 1000-10000 records), particularly since our network includes both Windows and Novell servers.

I assume you are suggesting going through the DTS wizard, saving the import code, converting it to a stored procedure (with variables), then calling the stored procedure from Access?

Or are you suggesting some other process?

And do you have any idea why it would take so much longer to load into the table when it's in SQL as compared to when it's in Access? I can understand some small difference since the table is in the application database rather than in a linked Access back end database, but not the big difference I'm experiencing.

Thanks,
Bob

 

[AlexCuse]

I'd imagine it is because access is doing the bulk of the work. How long is it taking anyway (for 10k records)?

This is the sub I use to call a DTS from the command line:

Private Sub CMD2_Click()
Dim SV As Variant
Dim DQ As String 
Dim s As String
Dim s1 As String 
Dim s2 As String
Dim s3 As String
Dim ServerName As String
Dim PackageName As String
Dim ExecutionLine As String

s = "dtsrun"
s1 = "/S"
s2 = "/E"
s3 = "/N"
ServerName = "Server Name"
PackageName = "Package Name"

ExecutionLine = s & " " & s1 & ServerName & " " & s2 & " " & s3 & PackageName

'Debug.Print ExecutionLine

SV = Shell(ExecutionLine)

MsgBox "DTS Running"

End Sub

DTS Progress is displayed in the DOS window. I think you also need SQL Server installed on the PC, so this may not be best solution for you.

This code does not allow manipulation of global variables unfortunately. I have some VB6 code that manipulates global variables, but this is much more to explain (involves activeX tasks within the package and such). I am not sure if this will work within Access or not, but if that sounds more appealing to you perhaps I can find link explaining it.

Good Luck,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[AlexCuse]

This may help to get you started. Let me know how it works out for you.

http://www.sqlservercentral.com/columnists/bknight/executingpackagefromvisualbasic.asp

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.