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

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

ngnam1988
Advocate

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

ngnam1988
Advocate
Advocate

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!

0 Likes
Reply
220 Views
0 Replies
Replies (0)