Get number of records in a table

Hi,

Newbie (complete idiot wrt to databases) here. I have a simple Access table (Table1) with 2 records. I trying to use an an ADODB connection (hope that makes sense) to write to this database. I need to know how many rows there are so when I right the next record I can define the number for the autonumber key field.


This is my code:

'Define a table recordset.
Set rsTable = .OpenSchema(adSchemaTables, Array(Empty, Empty, "Table1"))
Debug.Print rsTable.RecordCount
Do While Not rsTable.EOF
lngRows = lngRows + 1
rsTable.MoveNext
Loop
rsTable.MoveFirst

.RecordCount is returning -1
The Do ... While only counts to 1

What am I doing wrong? Thanks.

Its 1 line:
txtNumRecs = Dcount("*","table") (or Query)

ranman256,

Thanks but no joy. Dcount errors with not defined. Here is my entire code. The data base has the key field and the three data fields. I'm trying to writing a new record (three new pieces of field data) but I get an error that the number of query items does not match the field count. The issue is the first field or "key" autonumber. I have to know that number of records already in the Db so I can send the correct number for this field.

Or perhaps there is a better way.

Code:

---

Option Explicit
Const strDBFile As String = "D:\Demo Database.accdb"
Sub StartProcess()
Dim oConnection As Object
Dim strConnection As String
Dim arrData(2) As String
Dim rsTable As ADODB.Recordset, rsColumns As ADODB.Recordset
Dim lngColumns As Long, lngRows As Long
Dim strData As String, strSQL As String
    arrData(0) = "One"
    arrData(1) = "Two"
    arrData(2) = "Three"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBFile & ";"
    Set oConnection = New ADODB.Connection
    With oConnection
      On Error GoTo Err_Connect
      .Open strConnection
      'Define a table recordset.
      Set rsTable = .OpenSchema(adSchemaTables, Array(Empty, Empty, "Table1"))
      lngRows = DCount("*", "table")
      'Create columns record set.
      Set rsColumns = .OpenSchema(adSchemaColumns, Array(Empty, Empty, "" & rsTable("TABLE_NAME")))
      'Count columns in table.
      Do While Not rsColumns.EOF
        lngColumns = lngColumns + 1
        rsColumns.MoveNext
      Loop
      rsTable.Close
      rsColumns.Close
    End With
    strSQL = fcnGetStrSQL("Table1", lngRows, arrData)
    oConnection.Execute strSQL
   
lbl_Exit:
  Set oConnection = Nothing
  Set rsTable = Nothing
  Set rsColumns = Nothing
  Exit Sub
Err_Connect:
  MsgBox Err.Number & " " & Err.Description
  Resume lbl_Exit
End Sub
Function fcnGetStrSQL(strTableName As String, lngLastRecord, varData) As String
Dim strField_Headings As String
Dim strField_Values As String
Dim strData As String
Dim lngIndex As Long
 
  'Initialize SQL statement variable values.
  strField_Values = ""
  'strField_Values = lngLastRecord + 1 & ", "
  For lngIndex = 0 To UBound(varData)
    'Get field data
    strData = varData(lngIndex)
    'Build SQL statement.
    Select Case lngIndex
      Case Is = UBound(varData)
        strField_Values = strField_Values & "'" & strData & "'"
      Case Else
        strField_Values = strField_Values & "'" & strData & "'" & ", "
    End Select
  Next lngIndex
  fcnGetStrSQL = "INSERT INTO " & strTableName & " VALUES (" & strField_Values & ")"
Cleanup:
lbl_Exit:
  Exit Function
End Function

---

Code:

---

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
    Set .ActiveConnection = CurrentProject.Connection
  .Source = "SELECT * FROM table1"
  .LockType = adLockOptimistic
  .CursorType = adOpenKeyset
  .Open
  MsgBox .RecordCount
  .close
End With
Set rs = Nothing

---

The whole point of an autonumber field is that it increments automatically, you don't need to know anything about it.
And you can't tell what the next autonumber will be.
For example, if you deleted both records and added a new one, the autonumber would still be 3, it wouldn't start back at 1 unless you compacted the database first (because compacting rebuilds table indexes).

counting records is a single command. What errors can you get with that?

John,

Thanks for your post. The code helps, but I'm now at a loss.

My database has 4 fields. The first is ID. It was there when I created the data base. I added three more fields.

When I hit: oConnection.Execute strSQL

I get an error "Number of query values and destination fields are not the same" I understand that as my query only has 3 values and the data base has four fields. Until your reply, I thought that if I counted the records, and added 1 then I could make the first value in my SQL string the next sequential number and then add my three values.

You have explained that won't work.

So is there some way that I can rewrite the first line below so that it provides a query value (the first of four) that causes the ID field in the database reflect the autonum?

'strField_Values = lngLastRecord + 1 & ", "
For lngIndex = 0 To UBound(varData)

or do I simply have to remove the ID field autonum field?

Thanks

When I try to .Execute the

ranman256, when I put your line of code in my procedure I get "Compile error - sub or function not defined.

msgbox Dcount("*","qsMyQuery")

Dcount is a standard fcn in access.

Why are you concerned by the autonum field? Just ignore it.
An autonumber is simply a way of creating table indexes so that you can link tables together via queries. Read up on referential database design.
If you only have one table, you don't need the index. Forget about it or delete it.

To add data to a table you only need
[vba]currentdb.execute mysql[/vba]

I'm using a ADODB connection not Access. I'm trying to add a record to an Access database.

John,

I can delete it for this simply case, but in more advanced case it may be required.


My question boils down to this. The database has four fields

ID (which is the Primary and AutoNumber) Name1, Name2, Name3

I have data like this
Dim arrData(2)
arrData(0) = "Greg"
arrData(1) = "John"
arrData(2) = "ranman256)

I want to write that data to the data base. Right now my fcnGetStrSQL would return:
INSERT INTO Table1 VALUES ('Greg', 'John', 'ranman256')
If has three elements and it needs four. It needs be:
INSERT INTO Table1 VALUES ('#', 'Greg', 'John', 'ranman256') where '#" is whatever it needs to be to result in the next automnumber in the primary key ID field.

I've seen some post about and IDENTITY keyword and AUTOINCREMENT, but I can't figure out how to make either work even if it is applicable.

Can my fcnGetStrSQL be revised such that it will include a query value required to update the ID primary key with the autonumber? Thanks.

Specify the field names in your insert statement so the columns match up.

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

but don't set the value for the autonumber ;)

Unfortunately I won't always know the specific field names. Can that be done by reference to just a positional number (e.g., a number, e.g, Column1, Column2, etc)

If you tell me what you are try to do there might be a better/easier way of doing it.

But, you could add your fields to a query minus the ID and insert into that instead of the table.
If the fields in Access match the fields in your SQL Server(?) db you could:
query the msys tables to get a list of fields in the table or
open a recordset and iterate through the fields collection.
Or if the sql doesn't need to be completely dynamic you could save all your sql statements to a table, load the one you want and run it.

John,

Thanks for your interest and willingness to help. I am working on a larger project that will extract data from some fields in a collection of like forms or recurring forms (weekly report etc.). The form will be developed by an end user so it could have 3 elements like my simplified example or it could have hundreds of elements. When the elements are gather by the project, the user can export it to a Word table (small number of fields of course), an Excel spreadsheet, or an Access table.

It will be up to the end user to define the structure of their Word table, Spreadsheet, Access table. They will have to define the number of columns to match the number of fields they are extracting.

When a new table is created in access it automatically has the ID field with the AutoNumber data type. I was hoping to preserve that initial structure so that if their end use of the data extracted required support of other linked tables then it would be available. So if their common form has ten fields, then they simply add and define 10 additional columns and their headings for a total of 11 columns.

However the extracted data will only have 10 items which need to go into columns 2 though 11. I won't know the column names, and they won't have access to the underlying code that writes to Access.

So the question was and is. How do I write an SQL statement that will write the appropriate AUTONUMBER to column 1 then my variable number "X" of additional data to columns 2 through "X"

Back to the initial post. I am an idiot with databases and access. Just poking along with what I've scrounged around on the web and found. I feel like I got close but not all the way there. I have no idea what a SQL server is so you've lost me beyond that :-(

Not going to happen.

If it was so easy to develop reports everybody would be doing it.
If I'm wrong and you create such a thing you need to copyright it and sell it for millions.

Your sql, mysql, oracle, whatever, database will already have a primary key defined. The autonumber field inserted by access is only inserted if you create the table using a specific option. It means nothing in your case so you can forget about it. Figure out what the pk is for your db and use that.

John,

What is a "pk?" I've never imagined let alone mentioned any such object. I am a little bit arrogant when it comes to Word vba, but I find the Access group to be abnormally so.

Forget it. As I said at the beginning. I don't understand Access or ADODB. I thought I asked a reasonable question but obviously I haven't. I find it hard to believe that their isn't a structured answer to my question, but what do I know? Thanks for your effort. I'll scrap the project.

Pk is primary key.

You don't have to take my word for it. Get a second opinion. But I'd still suggest you learn a bit about databases and relational database design before you do.
Saying you are connecting via adodb means nothing. You can use dao and ado to connect to access. How can you not know what type of database you are connecting to?

Anyway, sorry if I seem arrogant.. :/ just tryin to help.

John, I did not intend to say that you singularly are an arrogant person. I apologize. I just think that you, like me, find it difficult to lets say "dumb down" your dialog to match that of the person needing help. I know a fair amount about Word VBA and sometimes find myself talking over the head of the party I want to help.

I know practically nothing about Access or databases. I admit that. To tell you the truth I don't really want to spend heaps of time trying to teach myself if I'm only going to discover that I can't to what would seem common and should be ridiculously to do.


It seems that to anyone who looked at the VBA code I've provided and knows about access and database that it would be apparent how I connected and what type of database? To my very limited knowledge, I am connecting via an ADODB connection:

Set oConnection = New ADODB.Connection

and I'm connecting to an Access database:

Const strDBFile As String = "D:\Demo Database.accdb"


Now, pursuing your suggestion that include the column names in my sql string I tried the following:

INSERT INTO Table1 ('Field1', 'Field2', 'Field3') VALUES ('One', 'Two', 'Three')

recall the access database has four columns. There headings are "ID, Field1, Field2 and Field3" I have confirmed this several times. Now when I run the code, I get:

"-2147217900 The INSERT INTO statement contains the following unknown field name 'Field1'. Make sure you have typed the name correctly, and try the operation again.

That is insane!

Just so I can understand correctly. Are you saying (or if you have quit) will someone else confirm. That if you have an access data base with Table1 that contains 3 simple columns headed "Field1 Field2 and Field3" that you can write to with the one of the following sql statements:

INSERT INTO Table1 VALUES ('One', 'Two', 'Three')
INSERT INTO Table1 ('Field1', 'Field2', 'Field3') VALUES ('One', 'Two', 'Three')

but that if you have a access database with four columns ID (type autonumber), Field1, Field2, and Field3 that it is impossible to write a new record with any type of sql statement. If that answer is "yes" then I have no choice but to humbly accept. If "no", then what statement do I need to use?

Thanks

Actually and with some assistance from another party, I've stumble on something that I think will work:

Code:

---

Option Explicit
Const strDBFile As String = "D:\Demo Database.accdb"
Sub StartProcess()
Dim oConnection As Object
Dim strConnection As String
Dim arrData(2) As String
Dim rsTable As ADODB.Recordset, rsColumns As ADODB.Recordset
Dim lngColumns As Long ', lngRows As Long
Dim strColumnNames As String, strSQL As String
    arrData(0) = "A"
    arrData(1) = "B"
    arrData(2) = "C"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBFile & ";"
    Set oConnection = New ADODB.Connection
    With oConnection
      On Error GoTo Err_Connect
      .Open strConnection
      'Define a table recordset.
      Set rsTable = .OpenSchema(adSchemaTables, Array(Empty, Empty, "Table1"))
      'Create columns record set.
      Set rsColumns = .OpenSchema(adSchemaColumns, Array(Empty, Empty, "" & rsTable("TABLE_NAME")))
      'Get columns count and names
      lngColumns = 0
      strColumnNames = ""
      Do While Not rsColumns.EOF
        lngColumns = lngColumns + 1
        strColumnNames = strColumnNames & ", " & rsColumns.Fields("COLUMN_NAME").Value
        rsColumns.MoveNext
      Loop
      'Strip ID field from names.
      strColumnNames = Replace(strColumnNames, ", ID", "")
      If Left(strColumnNames, 2) = ", " Then
        strColumnNames = Mid(strColumnNames, 3, Len(strColumnNames) - 2)
      End If
      rsTable.Close
      rsColumns.Close
    End With
    strSQL = fcnGetStrSQL("Table1", arrData, strColumnNames)
    oConnection.Execute strSQL
lbl_Exit:
  Set oConnection = Nothing
  Set rsTable = Nothing
  Set rsColumns = Nothing
  Exit Sub
Err_Connect:
  MsgBox Err.Number & " " & Err.Description
  Resume lbl_Exit
End Sub
Function fcnGetStrSQL(strTableName As String, varData, strHeadings) As String
Dim strField_Values As String
Dim strData As String
Dim lngIndex As Long
  'Initialize SQL statement variable values.
  strField_Values = ""
  For lngIndex = 0 To UBound(varData)
    'Get field data
    strData = varData(lngIndex)
    'Build SQL statement.
    Select Case lngIndex
      Case Is = UBound(varData)
        strField_Values = strField_Values & "'" & strData & "'"
      Case Else
        strField_Values = strField_Values & "'" & strData & "'" & ", "
    End Select
  Next lngIndex
  fcnGetStrSQL = "INSERT INTO " & strTableName & " (" & strHeadings & ") VALUES (" & strField_Values & ")"
  Debug.Print "INSERT INTO " & strTableName & " (" & strHeadings & ") VALUES (" & strField_Values & ")"
Cleanup:
lbl_Exit:
  Exit Function
End Function

---