Get Data from MS Access Database and Fill Result to Selected Column with same Row
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
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!