Using rcount after connecting to a database.

[USAMax]

I have a routine that calls a function that opens an Access accdb file but I cannot get a row count from the database.

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

 

[ChrisM]

row count would be:

rs.RowCount

Not sure why you need field count, you shouldn't be querying anything that you don't already know the composition/layout of. But...

rs.Fields.Count

Not sure what you are trying to do in your code, but it looks like you are trying to update values in the table. If that is true, you shouldn't be using "rs.AddNew" because that will add a blank record to the end of your table. Unless your variable rcount is the number of Excel rows you have to work through (I was assuming it meant the number of records in your Access table) and you really are adding new records.

Now I'm really confused, I'm not even sure why you need RowCount, what is it you are trying to do?

 

[USAMax]

Thank you Chris that will get me started:

I have small sheets of data that need to be updated in the Access database. Normal I would do a vlookup to find the row it is on then check to see if it needs to be updated but I am out of my element with Access from Excel. Here is an sample of my existing data:

Min Max
HSI 15000 20000
HHI 7000 12000
HKB 50 80
BBA 15000 20000
AIA 7000 12000
CLI 50 80
CTC 15000 20000
ABC 7000 12000

 

[ChrisM]

Since you are using Office 2007+, you can create linked tables in Access with your Excel data. They will show up in the table section of Access, with the arrow designating them as Linked, and they'll have the green Excel symbol. You can use them as you would any other table to perform joins and queries on.

In Access, go to the external data ribbon, hit the Excel button, and choose Link to Source Data.

 

[USAMax]

I thought of that but the database is too big and even in the few cases where it is just small enough to add the linked table, the file gets too big and takes too long to open.