How to reference rows and columns in Access VBA

Question

I have a template in excel with 22 columns, and around 50 rows. This template will be filled with field-report data, and I want to store each field report in an access database in the same type of template (22 columns + an ID column). How can I use VBA to refer to the rows and columns in access as I would in Excel?

For example, In excel in order to copy rows from one workbook to another, I would do:

Workbook("WorkbookName1").Worksheets("Worksheet 1").Range("A4:V4").End(xlDown).Copy _ Destination:= ThisWorkbook.Worksheets(1).Range("A4").Range(End(xlDown)))

Not clear what you're asking. Is it the WHERE clause, i.e. WHERE ID = 1234.? — A.S.H
I do not have much Access knowledge, but I know enough Excel VBA . I was hoping I could work with access similarly to how I work with worksheets. The database could have, say, 1 million records, and I just want to keep dumping report-data onto it by detecting the last record (last ID), and importing the 50 or so records from the Excel report at the end of it. Queries on the database will be done by someone else, I'd like to just touch VBA, not SQL. — ursulet
That seems to be a lost case IMO. You cant work with database objects without touching SQL. — A.S.H

ASH ASH · Accepted Answer · 2017-06-29T16:15:24

Access and Excel are VERY different. I think you want to automatically load data from multiple Excel files (templates) into your Access table, right. Check out the script below.

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Documents\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tablename"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
'       Kill strPathFile

      strFile = Dir()
Loop

Run the code from Access. It will import data from all Excel files in a folder.

How to reference rows and columns in Access VBA

1 Answers