I have a routine that calls a function that opens an Access accdb file but I cannot get a row count from the database.
How can I get a row count for the database? If anyone has a list of functions that work with Access accdb file please send them as I will need them. Access doesn't have columns but it has a list of fields. How do I get the field count? This is just one example but I am only working on the row count for now.
Thank you
Code:
Sub DBMgr()
Const XMLSht As String = "Imported XML" 'XML Import Worksheet
Const MDB As String = "DB.accdb" 'Primary Database
Const InsTbl As String = "tblInstrumentsInfo" 'Instrument Information Table from Access Database
ThisWorkbook.Activate
Sheets(XMLSht).Activate
Call MakeConnection(MDB, InsTbl)
For i = 1 To rcount - 1
rs.AddNew
rs.Fields("instrument_name") = Cells(i + 1, 1).Value
rs.Fields("min_price") = Cells(i + 1, 2).Value
rs.Fields("max_price") = Cells(i + 1, 2).Value
rs.Update
Next i
Call CloseConnection
End Sub
Public Function MakeConnection(ImptDB As String, TableName As String) As Boolean
'*********Routine to establish connection with database
'Dim db As database, rs As Recordset, r As Long
Dim DBFullName As String
Dim cs As String
DBFullName = Application.ActiveWorkbook.Path & "\" & ImptDB
cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFullName & ";"
Set cn = CreateObject("ADODB.Connection")
'Set cn = OpenDatabase(DBFullName)
If Not (cn.State = adStateOpen) Then
cn.Open cs
End If
Set rs = CreateObject("ADODB.Recordset")
If Not (rs.State = adStateOpen) Then
rs.Open TableName, cn, adOpenKeyset, adLockOptimistic
End If
End Function
How can I get a row count for the database? If anyone has a list of functions that work with Access accdb file please send them as I will need them. Access doesn't have columns but it has a list of fields. How do I get the field count? This is just one example but I am only working on the row count for now.
Thank you