Importing Data from excel using VB6 into Access Database

Question

Hi There,

Am a beginner in vb6 with a little knowledge of Access. I would like to import some record from an Excel (Spreadsheet) file into a Table in an existing Access Database  every month.

The Excel file serves as the monthly source data consisting of over two hundreds records. One of the table on the database will be updated (Append not Over-write) on a month basis by the source data received in form of Excel. The ID field on the table can accept duplicate value.

Please guide me on how to do that with a code to be used in a click event of a command button placed on a form to perform the action require.

Thanks for your anticipated help.

Amieen Designer

Answer 1

Hi Amieen,

You could create an ado connection and use this connection to export data to access. Here is a simple code in vba. Are you using VB6 or VBA? VB6 and vba are similar. You could try to convert it to VB6 code and adjust the sql string for you needed.

dbPath = "C:\Users\Documents\DataBaseForTesting.accdb"
  Set xlApp = CreateObject("Excel.Application")
  Set dbwb = xlApp.Workbooks.Open("C:\Users\Desktop\ForTest.xlsm")
  Set dbws = dbwb.worksheets("Sheet3")
  Set cn = CreateObject("ADODB.Connection")
  With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Mode = adModeReadWrite
    .ConnectionString = "Data Source=" & dbPath
    .Open
End With
  dsh = "[" & dbws.Name & "$]"
  sqlStr = "INSERT INTO Test ([Name], [Contact No], [Email ID]) "
  sqlStr = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbwb.Name & "]." & dsh
  cn.Execute sqlStr

For more information, you could take references:

Using Excel VBA to export data to MS Access table

Methods for transferring data to Excel from Visual Basic

Best Regards,

Terry

Answer 2

Hi Terry,

Thanks for your response to my problem. Am using VB6 but as you said, I will try to convert the code from VBA to VB6. I hope I will get it right. Thanks a lot, you really encouraged me.

I will give you a feedback on my progress.

Best Regards

Amieen

Answer 3

Hi Terry

I converting the code from VBA to VB6 and try to execute it.

I encounter a run-time saying subscript out of range on this line;

< Set dbws = dbwb.Worksheets("Sheet3")>

Where did I go wrong please?

Below is the code i used.

Thank you.

Amieen

Private cn As New ADODB.Connection
Private xlApp As Excel.Application
Private dbwb As Excel.Workbook
Private dbws As Excel.Worksheet

    
Private Sub Command1_Click()


  dbPath = "E:\MyTest\db11.mdb"
  Set xlApp = CreateObject("Excel.Application")
  Set dbwb = xlApp.Workbooks.Open("E:\MyTest\ForTest.xls")
  Set dbws = dbwb.Worksheets("Sheet3")
  Set cn = CreateObject("ADODB.Connection")
  
  
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Mode = adModeReadWrite
        .ConnectionString = "Data Source=" & dbPath
        .Open
    End With
  
  
  dsh = "[" & dbws.Name & "$]"
  sqlStr = "INSERT INTO Test ([Name], [Contact No], [Email ID]) "
  sqlStr = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbwb.Name & "]." & dsh
  cn.Execute sqlStr

End Sub

Answer 4

The error is indicating that there is no Worksheet with the name Sheet3 in the Workbook ForText.xls.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 5

Hi Paul,

Thanks for your observation. I really appreciate it. I have checked through my code and discovered

what you pointed out as the cause of the error.  I was able to detect and correct it together with other errors.

It's working perfectly fine now.

My sincere appreciation also goes to Terry X. for initial guide and "CODE" which has helped me a lot.

Thanks guys you have really saved my day.

Keep the good work.

Amieen.

Answer 6

Hi Paul

Am back with a little problem that needs your assistance.

I tried changing this query statement;

  sqlStr = "INSERT INTO Test ([Name], [Contact No], [Email ID]) "
  sqlStr = sqlStr & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbwb.Name & "]." & dsh
  cn.Execute sqlStr

to the following to enable me select a table from a dropdown list ;

 
  sqlStr = "INSERT INTO '" & cboTable.Text & "'  ([Name], [Contact No], [Email ID]) "
  sqlStr = sqlStr & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbwb.Name & "]." & dsh
  cn.Execute sqlStr

But during execution, it's prompting this error message;

Where did I go wrong please?

Thank you.

Amieen.

Answer 7

Hi Amieen,

Has your issue been resolved now? If it has, I would suggest you mark the helpful reply as answer. For the new issue, I suggest you remove  single quotation out of the cboTable.Text. 

Just like

sqlStr = "INSERT INTO '" & cboTable.Text & "'  ([Name], [Contact No], [Email ID]) "

Besides, I suggest you post a new thread if you have any other requirement. Thanks for understanding.

Best Regards,

Terry