getting data from Access using vba using data from excel

Question

I am trying to extract data from Access using a column of excel.

I have tried the following code but its not taking too much time when # of rows in excel exceed 5k rows. Does anyone know of a better way to reference the excel data to get the results:

Sub ddd()
Const dbloc As String = "C:\Users\mysystem\Downloads\Database11.accdb"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlbook As Workbook
Dim xlsheet As Worksheet 
Dim a As Long
Dim SQL As String


Set xlbook = ActiveWorkbook
Set xlsheet = xlbook.Worksheets(1)
xlsheet.Range("B2:Z100000").ClearContents

Set db = OpenDatabase(dbloc)

SQL = "SELECT Material, MPN  "
SQL = SQL & "FROM Sheet2 "

SQL = SQL & "WHERE Material IN ("
Dim r As Range
For Each r In Range("A2:A19098")
   SQL = SQL & r.Text & ","
Next r
SQL = Left(SQL, Len(SQL) - 1) 'Drop last comma
SQL = SQL & ")"

' i want to change this for loop because my range might vary from 80-100k 
  rows and this method is not working. i got a suggestion here that i can 
  use a table for this. But i am new to macros and access and not sure of 
  the syntax. Can anyone please help with the syntax. Assuming tablename for 
  the excel data is column1 and ranges from a2:a100000


Set rs = db.OpenRecordset(SQL)
', dbOpenSnapshot)
If rs.RecordCount = 0 Then
MsgBox "No data retrieved from database", vbInformation + vbOKOnly, "No 
Data"
    GoTo SubExit
Else
    rs.MoveLast
    recCount = rs.RecordCount
    rs.MoveFirst
End If
 xlsheet.Range("C2").CopyFromRecordset rs

End Sub

Any help will me much appreciated. Thanks!!

what do you mean using a column ? .... i think that you wanted to say ... but it is taking too much time ... ... please update your question — jsotola
– jsotola, Commented Sep 21, 2017 at 5:16
I implemented similar operation in Access - update data in table based on filtered recordset of key values. The best performance I achieved when used chunks of 100 keys per each query. You use one chunk with 19000 keys, which may be slow. Check if access table has index in key field. Also SQL text may exceed 64K in your case. — Sergey S.
– Sergey S., Commented Sep 21, 2017 at 5:43
Hi @jsotola, using a column here means, i am checking entries from a column in my excel wb to retrieve results from the access database. You are right, ... but it is taking too much time .... Do you know of a better way to approach this? I am new to access and macro so please help with the syntax as well thanks! — SQL_APPRENTICE
– SQL_APPRENTICE, Commented Sep 21, 2017 at 6:28
if your code works without error, but runs too slow, submit here for review .... codereview.stackexchange.com — jsotola
– jsotola, Commented Sep 21, 2017 at 6:43
do you know of a better way to reference the excel column? Using a table for example — SQL_APPRENTICE
– SQL_APPRENTICE, Commented Sep 21, 2017 at 6:43

Erik A · Accepted Answer · 2017-09-21 13:36:43Z

If you're looking for an alternative way to reference an Excel column, you can use the following:

SQL = "SELECT Material, MPN  "
SQL = SQL & "FROM Sheet2 "

SQL = SQL & "WHERE Material IN ("
SQL = SQL & "SELECT * FROM [A1:A19098] IN '"
SQL = SQL & ActiveWorkbook.FullName & "'"
SQL = SQL & " 'Excel 12.0 Macro;')"

This allows the DAO database engine to directly query your Excel file via OleDb, instead of passing each row as text, which should be more efficient.

The Excel file should be saved before you do this (and it's better to trigger it from a different file, but works for me when triggering from the same file).

Note that I am purposefully including 1 row more, because the first row contains column names when using this.

I'm assuming you're using a .xlsm file.

Collectives™ on Stack Overflow

getting data from Access using vba using data from excel

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related