VB3.0 Data Access Question

VB3.0 Data Access Question

Post by Carl Hilto » Tue, 10 Dec 1996 04:00:00



I have to import about 300K number of records into an access database.  I had unloaded VB3.0 from my
machine and have recently reloaded it.

1) Wasn't there something I had to do to get VB3.0 to read/write ACCESS2.0?

2) I have programmed my VB application to create a database and table.  My question is what is the easiest
means to put these 300K records into the database?

Is there a speed hit if I chose table over dynaset?  This is a single user database.

Right now it looks like I need to

For x = 1 to 300000
  MyTable.AddNew
  MyTable("Field1") = value
  MyTable("Field2") = value
  MyTable("Field3") = value
  MyTable("Field4") = value
  MyTable("Field5") = value
  MyTable.Update
next x

This would appear to be harddrive intensive and I was hoping for a more efficient method.

Sorry if this is in the wrong news group but I know what ..vb.database.odbc is and don't know what DAO or
RDO are so was not sure the appropriate group to post..

 
 
 

VB3.0 Data Access Question

Post by Victor A. Bob » Sun, 15 Dec 1996 04:00:00



> I have to import about 300K number of records into an access database.  I had unloaded VB3.0 from my
> machine and have recently reloaded it.

> 1) Wasn't there something I had to do to get VB3.0 to read/write ACCESS2.0?

> 2) I have programmed my VB application to create a database and table.  My question is what is the easiest
> means to put these 300K records into the database?

> Is there a speed hit if I chose table over dynaset?  This is a single user database.

> Right now it looks like I need to

> For x = 1 to 300000
>   MyTable.AddNew
>   MyTable("Field1") = value
>   MyTable("Field2") = value
>   MyTable("Field3") = value
>   MyTable("Field4") = value
>   MyTable("Field5") = value
>   MyTable.Update
> next x

> This would appear to be harddrive intensive and I was hoping for a more efficient method.

> Sorry if this is in the wrong news group but I know what ..vb.database.odbc is and don't know what DAO or
> RDO are so was not sure the appropriate group to post..

As for your first question for VB3 to use Access 2.0 you have to install
the Access 2.0 compatability layer that are just a few files you should
be able to download from MS or any of the good VB sites such as Carl &
Gary's.

As for the 300k records that's quit alot. If you are importing them from
a current database you can use the INSERT command to Import the records
into your new database. If you only need to import 1 record I suggest
you Index your database and use the SEEK command to locate that record
and just add that 1 record to your database. To use the INSERT command
on an external database you will have to open it as well as the one you
want to import into. All this can be found in the VB Help files under
opening and external database using the Data Access Object.

Best of Luck


 
 
 

VB3.0 Data Access Question

Post by Christian Lotit » Tue, 17 Dec 1996 04:00:00



> I have to import about 300K number of records into an access database.  I had unloaded VB3.0 from my
> machine and have recently reloaded it.

> 1) Wasn't there something I had to do to get VB3.0 to read/write ACCESS2.0?

> 2) I have programmed my VB application to create a database and table.  My question is what is the easiest
> means to put these 300K records into the database?

> Is there a speed hit if I chose table over dynaset?  This is a single user database.

> Right now it looks like I need to

> For x = 1 to 300000
>   MyTable.AddNew
>   MyTable("Field1") = value
>   MyTable("Field2") = value
>   MyTable("Field3") = value
>   MyTable("Field4") = value
>   MyTable("Field5") = value
>   MyTable.Update
> next x

> This would appear to be harddrive intensive and I was hoping for a more efficient method.

> Sorry if this is in the wrong news group but I know what ..vb.database.odbc is and don't know what DAO or
> RDO are so was not sure the appropriate group to post..

If you want to do it one record at a time your best bet is to use
transactions.

Your code would look something like this
Set Wrk = Workspaces(0)
Set rst = OpenRecordset("mytable",dbOpenDynaset)

Wrk.BeginTrans 'Start the transaction
rst.MoveFirst
Do While Not rst.Eof

..You can do your update here..

rst.MoveNext
Loop

Wrk.Commitrans 'End the transaction
                This will force all the data to written only at this            point.

                If there is an error and you want to undo your update you
                can use Wrk.Rollback before Wrk.Committrans

Hope this helps.

Christian.

 
 
 

1. VB3.0 Data Access question

I have to import about 300K number of records into an access database.  I had unloaded VB3.0 from my
machine and have recently reloaded it.

1) Wasn't there something I had to do to get VB3.0 to read/write ACCESS2.0?

2) I have programmed my VB application to create a database and table.  My question is what is the easiest
means to put these 300K records into the database?

Is there a speed hit if I chose table over dynaset?  This is a single user database.

Right now it looks like I need to

For x = 1 to 300000
  MyTable.AddNew
  MyTable("Field1") = value
  MyTable("Field2") = value
  MyTable("Field3") = value
  MyTable("Field4") = value
  MyTable("Field5") = value
  MyTable.Update
next x

This would appear to be harddrive intensive and I was hoping for a more efficient method.

Sorry if this is in the wrong news group but I know what ..vb.database.odbc is and don't know what DAO or
RDO are so was not sure the appropriate group to post..

2. Oracle & MS Access

3. question reg. MS-Access database access using VB3

4. Format the output of a stored proc

5. VB3 (yes, VB3!) database question

6. How to program a new ODBC driver ?

7. VB3.0 / Access 2.0 OLE Question

8. VB3/Access 2.0 Stupid Beginner Question

9. HELP: ODBC with Access 2.0, VB3.0, and Data Control

10. Accessing oracle7 Long data type with VB3