VB6 - Cut data from Excel paste to Access 1

[ImGettingThere]

This is my 1st post. I'm a not a VB6 novice and i'm a huge way away from being an expert so be kind.

I receive 6 spreadsheets each month. Each spreadsheet has 16 columns and in total they contain roughly 140,000 rows of data.

I plan to use VB6 to build a front end that will copy the data from the spreadsheets and place it into an Access database. So that a wide range of queries and statistics can be pulled from it.

As a test (using DAO 3.6)I set the code up so that it looped through each line of data and imported it one by one. In all it took 55 minutes to import 137,000 records. Ouch!

If I were implement this into the live environment my network would explode. My boss would be standing at my door with a red face and with my hat and coat in hand.

The database table has been set up to replicate the column structure of the spreadsheet.

My question - is there a way that I can lift the whole chunk of data from the spreadsheet and paste it directly into the database table in one go?

I've heard that I might be able to do it using the ADO control. Having never used ADO I don't know where to start.

Do I even have to use access? is there an efficient way that I can read and query the data straight from the 6 spreadsheets Excel?

Can anyone shed any light on this? As I said, be kind.

Code examples would be greatly appreciated.

regards

 

[Davidprince]

I think this may solve your problem but am unsure as to how quick it is. If you search:

convert+VBA+program+to+VB in Dogpile

Item 41 on the resulting list is:
Covert Database Data Between Serveral Format - visual basic, vb, vbscript

Note: the misspelling of Several is correct.
This will get you to planetsourcecode and a converter which can be downloaded.
Regards
David

 

[Swi]

This code is taken from the thread by strongm (thread222-1213437) and manipulated to fit your needs.

Private Sub Command1_Click()
    Dim sConTxt As String
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    sConTxt = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Test.xls;" & _
    "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""
    
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cnn.Open sConTxt 'open the connection
    
    ' Create new table with data in target database
    rs.Open "Select *  INTO [Table1] IN 'c:\tektips.mdb' " & _
    "FROM [Sheet1$]", cnn
    
    ' or Append to existing table in target database
    'rs.Open "INSERT INTO [Table1] IN 'c:\tektips.mdb' " & _
    "Select *  FROM [Sheet1$]", cnn

    ' Prompt user of completion
    MsgBox "Done!", vbInformation
End Sub

Swi