This week I have to create an Access Form and implement VBA to extract the data from an Excel spreadsheet and display it in an Access listbox, dropdownbox, or any form of grid.
In First Try, you had an error or two. And I changed your code a little: some of the names you used were too long for my taste.
Code:
Private Sub Form_Load()
' Dim dbsWk7_Magaw_INFO262_CD_DVD As DAO.Database '<<=this was too long for me
Dim db As DAO.Database
' Dim frmM As DAO.Recordset '<< didn't make sense (to me) to have a record set declared with a form prefix
Dim rs As DAO.Recordset ' record set
Dim varRecords As Variant
Dim intNumReturned As Integer
Dim intNumColumns As Integer
Dim intColumn As Integer
Dim intRow As Integer
Dim strSQL As String
On Error GoTo ErrorHandler
Set db = CurrentDb
strSQL = "Select MediaID, MediaType, Title, Artist, Producer_Studio FROM ExcelLinkTable1"
'you had:
' Set frmM = dbsWk7_Magaw_INFO262_CD_DVD.OpenRecordset(SQL, dbOpenSnapshot)
'but you declared "Dim strSQL As String"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
' Space
varRecords = rs.GetRows(3)
intNumReturned = UBound(varRecords, 2) + 1
intNumColumns = UBound(varRecords, 1) + 1
For intRow = 0 To intNumReturned - 1
For intColumn = 0 To intNumColumns - 1
Debug.Print varRecords(intColumn, intRow)
Next intColumn
Next intRow
rs.Close
' dbsWk7_Magaw_INFO262_CD_DVD.Close
'The rule is: If you create it destroy it,
' If you open it, close it.
' you didn't open dbsWk7_Magaw_INFO262_CD_DVD, so don't close it.
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
msgbox "Error #: " & Err.Number & vbCrLf & Err.Description
End Sub
The code now should print to the immediate window. (at least it did for me)
BTW, I learned something from you today. I didn't know about the GetRows method. I'll have to study it - I'm sure it will be useful....thanks.