none
Importing Data from excel using VB6 into Access Database RRS feed

  • 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

    Tuesday, June 20, 2017 10:55 AM

All replies

  • 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

    Wednesday, June 21, 2017 7:32 AM
  • 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
    Wednesday, June 28, 2017 9:43 AM
  • 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


    • Edited by Amieen Wednesday, June 28, 2017 4:08 PM
    Wednesday, June 28, 2017 4:07 PM
  • The error is indicating that there is no Worksheet with the name Sheet3 in the Workbook ForText.xls.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, June 28, 2017 7:10 PM
  • 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.

    • Marked as answer by Amieen Friday, June 30, 2017 10:51 AM
    • Unmarked as answer by Amieen Friday, July 7, 2017 7:41 AM
    Friday, June 30, 2017 10:51 AM
  • 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;

    Syntax error in query. Incomplete query clause.

    Where did I go wrong please?

    Thank you.

    Amieen.

    Thursday, July 6, 2017 1:21 PM
  • 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


    Saturday, July 29, 2017 6:20 AM