Get number of records in a table

[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