Database Discussion - Copying Records from a Large DB to anothe

Print Page | Close Window

Copying Records from a Large DB to anothe

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=6994
Printed Date: 30 March 2026 at 3:24pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Copying Records from a Large DB to anothe

Posted By: fark
Subject: Copying Records from a Large DB to anothe
Date Posted: 04 November 2003 at 11:07am

I'm trying to copy records from one large Access DB (1000 Records) and adding them to another DB. I keep getting Unspecified errors because I think I'm using to many resources. I'm looking for suggestions on how to solve this problem. I'm not even sure what questions to ask. I'm limited to Personal Web Server because I'm running Win98 and my company isn't going to spend any money on software. Is there anything free out there that will work better and run on win98? I know I'm asking for a lot and there may be nothing I can do but I'm out of ideas. Thanks so much for all the help I've gotten here.

Replies:

Posted By: michael
Date Posted: 04 November 2003 at 2:38pm

1000 records is not much, you can just export the data from one mdb to another. No web server needed.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Gullanian
Date Posted: 04 November 2003 at 2:54pm

You could also write a quick ASP script to loop through the records adding them to the other access database

Posted By: fark
Date Posted: 04 November 2003 at 4:04pm

I know that a 1000 records isn't that much but I just can't get my script to work with that many. Maybe if I post the code. This code works fine for the first 60 records and then stops with:

Microsoft JET Database Engine error '80004005'

Unspecified error

/convertups.asp, line 76

Am I opening something wrong? Am I using the resources wrong? I've tried a lot of different things and am stumped for something else to try. Thanks for your help.

<%
Response.Write "TEST"
Const DSN_NAME = "DSN=UPS Tracking"
Dim dcUPS, rsUPS
Dim ConFields
Dim recordid
Dim fieldname(), value()
Dim strSQL
Dim strSQL1

Const DSN_SD = "DSN=Shipping Database"
Dim dcSD
Set dcSD = Server.CreateObject("ADODB.Connection")
dcSD.ConnectionTimeout = 4
dcSD.CommandTimeout = 2
dcSD.Open DSN_SD

Dim rsSD

Set rsSD = Server.CreateObject("ADODB.Recordset")

strSQL1= "SELECT * FROM UPS;"
rsSD.Open strSQL1, dcSD, adOpenKeyset, adLockPessimistic

ConFields=LoadFile("convertups.con")
Set dcUPS = Server.CreateObject("ADODB.Connection")
dcUPS.ConnectionTimeout = 4
dcUPS.CommandTimeout = 2
dcUPS.Open DSN_NAME, DSN_USER, DSN_PASS
Set rsUPS = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM calPackage, calPkgAccessory, calShipment, calPkgAgent WHERE calPackage.m_primaryKey = calPkgAccessory.m_foreignKey AND calPackage.m_foreignKey = calShipment.m_foreignKey AND calShipment.m_foreignKey00 = calPkgAgent.m_primaryKey;"

rsUPS.Open strSQL, dcUPS, adOpenStatic, adLockReadOnly

Do While Not rsUPS.EOF
recordid=checkduplicate(rsUPS.Fields("Sm_trackingNo"))
If recordid<>0 Then
  ReDim fieldname(UBound(ConFields)/2)
  ReDim value(UBound(ConFields)/2)
  I=0
  T=1
  Do While I<UBound(fieldname)
   fieldname(I)=ConFields(T)
   value(I)=CStr(rsUPS(ConFields(T-1)))
   T=T+2
   I=I+1
  Loop

rsSD = storeshipfield (rsSD, fieldname, value)

End If
rsUPS.MoveNext
Loop
rsSD.Update
rsSD.Close
Set rsSD = Nothing
rsUPS.Close
Set rsUPS = Nothing
dcUPS.Close
Set dcUPS = Nothing

Response.Write "END"

Function checkduplicate(trackingnumber)
Dim rsSD
Dim strSQL
Dim NewID
Dim DB_CONNECTIONSTRING
DB_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Shipping Database\shippingdatabase.mdb;"
Set rsSD = Server.CreateObject("ADODB.Recordset")

strSQL= "SELECT * FROM UPS WHERE TrackingNumber = """ & trackingnumber & """;"
rsSD.Open strSQL, DB_CONNECTIONSTRING, adOpenFowardOnly, adLockReadOnly

If rsSD.EOF Then
  rsSD.Close
  Set rsSD=Nothing
  checkduplicate=1
Else
  rsSD.Close
  Set rsSD=Nothing
  checkduplicate=0
End If

End Function

Function storeshipfield(rsSD, fieldname, value)
rsSD.AddNew 'Line 90
rsSD("ParcelCarrier")="UPS"

For I=0 To UBound(fieldname) - 1
rsSD(fieldname(I))=CStr(value(I))
Next
Response.Write "Record " & rsSD("ID") & " Updated <BR>"
storeshipfield=rsSD
End Function
%>

Posted By: michael
Date Posted: 05 November 2003 at 11:44am

Way to complicated. Use an append query like:

INSERT INTO newtable IN 'c:\dir\newdatabase.mdb'
SELECT *
FROM Table;
while you are connected to the old database

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: fark
Date Posted: 06 November 2003 at 7:31am

Thanks, that's what I needed to know. I figured there was probably a way that was 10 times easier. I'm new to both asp and sql so I didn't really know about the INSERT Statement. Thanks