Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
ngnam1988
219 Views, 0 Replies

Get Data from MS Access Database and Fill Result to Selected Column with same Row

Dears,
I'm trying working with MS Access Database with iLogic. At the moment I can connect to database, I can get the data but I can't take the result to selected column with same row with lookup cell's value. Please help me. Here my code that I run when working with opened excel file:

 

 

Sub Read()
    Dim cn As Object, rst As Object
    Set cn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0;"
        .ConnectionString = "Data Source=E:\Database.accdb;Persist Security Info=False;"
        .Open
    End With
    Dim strQuery As String
    Dim oCODE As String
    Dim oCELL As String
    Dim oRCELL As String
    For Each i In Range("A20:J30").Rows
        oCELL = i.Cells(1, 1).Value
        oRCELL = i.Cells(1, 9).Address
        If Left(oCELL, 3) = "000" Then
            oCODE = "000CODE"
        End If
        On Error Resume Next
        strQuery = "SELECT [WEIGHT] FROM " & oCODE & " WHERE [CODE]='" & oCELL & "'"
        rst.Open strQuery, cn
            Sheets("BOM").Range(oRCELL).CopyFromRecordset rst
        rst.Close
        cn.Close
    Next
End Sub

 

 

Range to check and get data from database is A20:A30

Range to return result that I want is J20:J30

Ref:
https://adndevblog.typepad.com/manufacturing/2017/08/connecting-microsoft-access-via-inventor-ilogic...

https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/read-retrieve-information-from-a-ms-...
https://forums.autodesk.com/t5/inventor-ilogic-and-vb-net-forum/how-to-connect-to-ms-access-database...

Please help me what wrong in my code. And give me the solution. Thanks you very much!