Using SQL to copy data from Excel to Access in VB 5

[vza]

I was wondering if there was a way to copy data from an excel spreadsheet into a Access Database from a VB Application. This is what I have so far...I keep getting an error saying the database engine cannot find sheet1 (of the Excel workbook). Any responses would be greatly appreciated.

Private Sub ConvertButton_Click()
    ' Variables
        Dim strquery As String
    
    ' Connection Properties
        Dim AccessCn As New ADODB.Connection
        
        AccessCn.Provider = "Microsoft.Jet.OLEDB.4.0;"
        AccessCn.Open AccessText.Text, LoginForm.UsernameText.Text, LoginForm.PasswordText.Text
        strquery = "SELECT * INTO [CONVERSIONS] IN '" & AccessText.Text & "' FROM [Sheet1$]"
        
        AccessCn.Execute strquery

[\code]
AccessText and ExcelText are text boxes which contain the paths for the Database and spreadsheet, respectively. There also exists a Login Form in which the user specifies a username and password for logging onto Access DB.

Thanks
-vza

 

[vza]

Is there even a way to use SQL to accomplish this task?? Should I be trying something else??

Thanks
-vza

 

[Spellman]

What your doing here is opening up your access database and creating a query that is trying to move data from one access table to another so it's looking for an Access table named [Sheet1$]. You need to set up a connection to an excel document or save the excel document as a csv file and parse it and then load the table through code. Thats possibly overcomplicating it though and your best bet would be to look into opening the Excel document using an excel driver and going through it that way. I guess to summarise It is possible to do though but probably a lot more complicated than what your trying to establish here.

 

[AndyWatt]

This should get you started:

  Dim conX As ADODB.Connection
  Set conX = New ADODB.Connection
  conX.Open "DSN=Excel Files;DBQ=C:\My Documents\Book1.xls;" _
    & "DefaultDir=C:\My Documents;DriverId=22;" _
    & "MaxBufferSize=2048;PageTimeout=5;"
  conX.Execute "SELECT Field1, Field2, Field3 INTO MyTable " _
    & "IN 'C:\My Documents\db1.mdb' FROM [Sheet1$]"
  conX.Close
  Set conX = Nothing

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"Why does my program keep showing error messages every time something goes wrong?"

 

[talln6e]

I am doing something very similar -

With objConnection
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & strXlsFile & ";Extended Properties=""Excel 8.0;HDR=No;MaxScanRows=16;"""
.Open
End With

strQuery = strQuery & " from [cd_stats_sample$]"

This is working fine, but with 2 problems:

1 - The xls file has no heading, yet with HDR=No,
the query still only gets rows 2 and greater.

2 - Many of the rows have numbers, but some of them
have an asterisk too, or a leading space, like " 13.5* ".
Yet even with MaxScanRows=16, the query comes up with
blanks for those values.

Any ideas, anybody?

Thanks.
Howie

 

[vza]

Sorry fellas...I appreciate the responses....I have started a new project so I have been unable to work on this one... I will take all your responses into account...i will let you know how it turns out.

Thanks, appreciate the help.
Enjoy the weekend.

-vza

 

[Swi]

talln6e,

I have run into this problem before and I resolved it by following the instructions in this link:

http://support.microsoft.com/default.aspx?scid=kb;en-us;194124

Swi

 

[CCLINT]

This may also help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

 

[vza]

Sorry its been so long....been very busy as of late.

I appreciate all the help regarding this issue.
stars all around.....

thanks
-vza