Get number of records in a table

[gmaxey]

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.

[ranman256]

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

[gmaxey]

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.



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

[jonh]

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).

[ranman256]

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

[gmaxey]

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

[gmaxey]

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

[ranman256]

msgbox Dcount("*","qsMyQuery")

Dcount is a standard fcn in access.

[jonh]

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
currentdb.execute mysql

[gmaxey]

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

[gmaxey]

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.

[jonh]

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 ;)

[gmaxey]

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)

[jonh]

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.

[gmaxey]

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 :-(

[jonh]

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.

[gmaxey]

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.

[jonh]

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.

[gmaxey]

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

[gmaxey]

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


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

[jonh]

I haven't given up, but I wont be around for a week or so after today.
Giving the field names in the sql statement should work. If it doesn't I don't know why but then I don't understand the way you are opening the recordset. I've never seen it done that way before, I always use the sort of code I wrote in post #4.

So you are connecting to Access. Is the database that you are connecting to another file or the same file that you're coding in?
I would suggest that you build the application in one Access file to start off with. Tables, queries, forms, everything all together.
Then, as I said before, you don't need to bother with connections. All you need to write to add, delete or update data is, for example

currentdb.execute fcnGetStrSQL

Once you have everything working the way you want you can split it up and use whatever connection method you like.

[gmaxey]

John,

Thanks. What I thought was a solution turned out to be false. The column names were listed alphabetical instead of in the order they appear in the table to so the data was going to the wrong fields :-(. I've cobbled together a partial work around, the only requirement is that there be at least one record defined in the database. Before I post that code and ask a different question, I'll try to address your comments.

I am writing the code in Word, because that is what I know and the final product will be a Word template add-in. I don't know how to write code in Access or know if there is such a thing as a Access template Add-in. I don' t know how to create Com addins and I don't want to learn at this point.

Here is the code that is working best so far. It has one unresolved issue.

The Access data base has two tables "Table1" and "Table2" Table1 has four fields, the default "ID" autonumber fields, and three shorttext fields Field1, Field2 and Field3. Table2 has three shorttext fields Field1, Field2 and Field3 (I deleted the default ID field).

Both databases have 0 records

When I run Sub Demo, the attempt to write data to Table1 errors because there 1) The number of query items does not match the number of fields in the database and, 2) there is no record to parse for column names. The attempt to write data to Table2 is successful


If I manually add a record to Table1 and run Sub Demo again then data is written to both tables as expected.

The remaining question is: Is there a way to determine the column names of a table "in the order they appear in the table" if the database is empty (contains 0 records)? All of the other methods I've found return the column names in alphabetical order.


Option Explicit
Const strDBFile As String = "D:\Demo Database.accdb"
Sub Demo()
DemoWriteToDB "Table1"
DemoWriteToDB "Table2"
End Sub
Sub DemoWriteToDB(strTableName As String)
Dim oConnection As Object
Dim strConnection As String
Dim arrData(2) As String
Dim rsRecords As ADODB.Recordset, rsColumns As ADODB.Recordset
Dim lngINdex As Long, lngColumns As Long
Dim strColumnNames As String, strSQL As String
arrData(0) = "111"
arrData(1) = "222"
arrData(2) = "333"
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBFile & ";"
Set oConnection = New ADODB.Connection
With oConnection
On Error GoTo Err_Connect
.Open strConnection
Set rsRecords = CreateObject("ADODB.Recordset")
rsRecords.Open "SELECT * From " & strTableName & ";", oConnection, adOpenStatic
'Create columns record set.
Set rsColumns = .OpenSchema(adSchemaColumns, Array(Empty, Empty, "" & strTableName))
'Get columns count.
lngColumns = 0
Do While Not rsColumns.EOF
lngColumns = lngColumns + 1
rsColumns.MoveNext
Loop
'It appears that the only way to get the column names in the order that appear in the table _
is to use the fields name property. This requires there to be at least one record.
If rsRecords.RecordCount > 0 Then
rsRecords.MoveFirst
'If the number of columns in the table don't match the number of data elements in the data array then _
then we have to write to target columns by name:
If Not UBound(arrData) + 1 = lngColumns Then
'Get the column names.
strColumnNames = ""
rsRecords.MoveFirst
For lngINdex = 0 To lngColumns - 1
strColumnNames = strColumnNames & ", " & rsRecords.Fields(lngINdex).Name
Next
'If an autonumber "ID" column exists, we need to strip it out.
strColumnNames = Replace(strColumnNames, ", ID", "")
If Left(strColumnNames, 2) = ", " Then
strColumnNames = Mid(strColumnNames, 3, Len(strColumnNames) - 2)
End If
End If
End If
rsRecords.Close
rsColumns.Close
End With
strSQL = fcnGetStrSQL(strTableName, arrData, strColumnNames)
oConnection.Execute strSQL
lbl_Exit:
Set oConnection = Nothing
Set rsRecords = Nothing
Set rsColumns = Nothing
Exit Sub
Err_Connect:
Select Case Err.Number
Case -2147467259
MsgBox "The number of data elements in the extracted data do not match the number of fields in the data base." & vbCr + vbCr _
& "This is often the result of the default ""ID"" autonumber field in an Access database." & vbCr + vbCr _
& "This condition cannot be resolved because there are no records in the database to evaluate." & vbCr + vbCr _
& "To resolve this issue, you can manually define the first record in your database and try again."
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
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
If Not strHeadings = vbNullString Then
fcnGetStrSQL = "INSERT INTO " & strTableName & " (" & strHeadings & ") VALUES (" & strField_Values & ")"
Else
fcnGetStrSQL = "INSERT INTO " & strTableName & " VALUES (" & strField_Values & ")"
End If
Cleanup:
lbl_Exit:
Exit Function
End Function

[jonh]

I created a table (table1), added an autonumber (id) and three text fields (x, y and z)

In the below code, the table is emptied, field names are queried and a new record is added with field names defined in reverse order.


Sub test()

'create new connection object
'Dim conn As New ADODB.Connection
Set conn = CreateObject("ADODB.Connection")

'define connection string and open the connection
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test.accdb;"
conn.Open

'delete all data from the table to test that field names are still available
conn.Execute "delete * from table1"

'open a recordset and get list of field names
'Dim rs As New ADODB.Recordset
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = conn
rs.Open "select * from table1"
For Each f In rs.Fields
Debug.Print f.Name, f.Type
Next
rs.Close
Set rs = Nothing
'result =
'ID 3
'x 202
'y 202
'Z 202

'insert new record - (fields defined in reverse order)
conn.Execute "insert into [table1] (z,x,y) values ('zzz','xxx','yyy')"
conn.Close
Set conn = Nothing

End Sub