Friends,
I need to insert around 13000 records from a sybase external DB to one
of my local table in access. I have the connection string set up and
everything works fine except for the execution time. It takes a lot of
time to populate the local table because I have a recordset variable
which loops through the 13000 records returned from the ADODB
connection object and inserts one line after the other into the local
table. Do you have any better alternative to approach the time issue?
Any help in this regard will be great.
Pseudocode:
Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset
adoConn.Provider = "--"
adoConn.ConnectionString = "CONNECTION STRING STATEMENT"
adoConn.Open
sSQL = PASS THROUGH QUERY SELECT STATEMENT
adoConn.Execute (sSQL)
adoRS.Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
adoRS.MoveFirst
Do While Not adoRS.EOF
INSERT INTO LOCAL ACCES TABLE RECORD STATEMENT
adoRS.MoveNext
Loop
5  16822  NeoPa 32,556
Expert Mod 16PB
Access has the ability to link to tables in remote databases.
If you use this then you can execute an action query to do the work.
This will execute MANY MANY times faster than looping through the recordset in VBA.
Access has the ability to link to tables in remote databases.
If you use this then you can execute an action query to do the work.
This will execute MANY MANY times faster than looping through the recordset in VBA.
How does a linked table work, after linking the table, if I delete the linked table from access, does it delete it from the external database too or is it just a view of the table present in external DB?
NeoPa 32,556
Expert Mod 16PB
How does a linked table work, after linking the table, if I delete the linked table from access, does it delete it from the external database too or is it just a view of the table present in external DB?
It's just a view.
You can delete it from within your code if you want.
PEB 1,418
Expert 1GB
In fact with your code it seems that you do very well te connection to Sybase...
Well Done..
But the better way is to insert your records together not one by one...
Use the following structure..
Insert INTo MyAccessTable(Field1, Field2, Field3)
Select Field1,Field2, Field3
FROM MySybaseTable;
:)
Another way of doing this would be to open the new table as a second recordset. -
-
Dim adoConn As New ADODB.Connection
-
Dim adoRS As New ADODB.Recordset
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
-
adoConn.Provider = "--"
-
adoConn.ConnectionString = "CONNECTION STRING STATEMENT"
-
adoConn.Open
-
sSQL = PASS THROUGH QUERY SELECT STATEMENT
-
adoConn.Execute (sSQL)
-
adoRS.Open sSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
-
-
set db = CurrentDB
-
Set rs = db.openrecordset("Local Table Name")
-
-
adoRS.MoveFirst
-
Do While Not adoRS.EOF
-
rs.AddNew
-
rs!FieldName1 = adoRS!FieldName1
-
rs!FieldName2 = adoRS!FieldName2
-
rs!FieldName3 = adoRS!FieldName3
-
' etc.
-
rs.Update
-
adoRS.MoveNext
-
Loop
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: Damon Grieves |
last post by:
Hi
I just want to be sure I understand how the Access client works. If I have
an Access back end with a million records on a server and an Access client.
If the client is installed on the users pc...
|
by: Bob Alston |
last post by:
I have a routine to copy data to new versions of my app via insert into
sql statements. Unfortunately, due to evolution of my app, sometimes
the new version has more restrictive editing than an...
|
by: bbasberg |
last post by:
Hello,
I thought I would do a "sanity check" by asking the experts how to do the following:
I need to process external data (it will be in an Access DB table and will be updated daily) into a...
|
by: lesperancer |
last post by:
I've got an access97 reporting mdb that pulls data (77,000 rows) from a
sql server table into a local table to run reports
if the local table is part of the reporting MDB, the insert statement...
|
by: rn5a |
last post by:
During registration, users are supposed to enter the following
details: First Name, Last Name, EMail, UserName, Password, Confirm
Password, Address, City, State, Country, Zip & Phone Number. I am...
|
by: eighthman11 |
last post by:
Hi everyone:
Using Sql Server SQL 8
I'm trying to INSERT records into a "can software package" batch
table. I have a work-table that mimics the batch table. After
manipulating the records in...
|
by: SaltyBoat |
last post by:
Needing to import and parse data from a large PDF file into an Access
2002 table: I start by converted the PDF file to a html file. Then
I read this html text file, line by line, into a table...
|
by: mrajanikrishna |
last post by:
Hi,
I am importing external data(oracle) to local sql server DB thru
linked server. This is actual replica of the external data(not
normalized). I need to run this import for every 2 minutes....
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
