Using rcount after connecting to a database.

USAMax

Well-known Member
Joined
May 31, 2006
Messages
846
Office Version
  1. 365
Platform
  1. Windows
I have a routine that calls a function that opens an Access accdb file but I cannot get a row count from the database.

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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top