VBA problem getting Excel to report the correct number of fields for an Access table

[placebo2000]

I'm using Excel 2007 & Access 2007.

I'm having a problem getting Excel to report the correct number of fields for an Access table. Days of googling and reading haven't provided with a solution that works!

When using the (unfinished) Addcolumn sub below in Excel, the MsgBox results are 11 Records and 9 fields (I commented out the alter table code while testing). When I run this code in the Access immediate window:

msgbox currentproject.Connection.Execute("Squirrel", , adCmdTable).Fields.Count

The above code gives me the correct results, Which is currently 202 fields (key, Date/Time, Temperature or voltage × 200 fields).

I noticed that in my sub it doesn't refer to the table "Squirrel", is this where I'm going wrong?

What I'm trying to do is count the number of fields in the database. Then I need to test whether the CSV data I have processed in Excel has more columns than the fields currently in the database. If so, then I will create new fields to allow the data to be pushed to the Access database.

I'll also need to test that the data I'm adding isn't older than the last piece of data in the table, but I'll cross that bridge later!

Sub AddColumntoSquig()
      Dim conn As New ADODB.Connection
      Dim StrNewColumn As String
      Dim rsT As ADODB.Recordset
      Dim intTblCnt As Integer, intTblFlds As Integer
      StrNewColumn = "ALTER TABLE Squirrel Add [Chan 201] DEC;"
     With conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .CursorLocation = adUseClient
        .Open "Data Source=C:\Documents and Settings\xxxx\My Documents\My Data Sources\Test1.accdb"
        .Execute StrNewColumn
      End With
     Set rsT = conn.OpenSchema(adSchemaTables)
      intTblCnt = rsT.RecordCount
      intTblFlds = rsT.Fields.Count
      MsgBox ("Records: " & intTblCnt & ";" & vbCrLf & "Fields: " & intTblFlds)
     conn.Close
     Set conn = Nothing
  End Sub

FIO: "Squirrel" refers to our "Eltek Squirrel Telemetric Temperature Monitoring System & Automated Alarm Callout System". It monitors all of our Fridges, Freezers and Incubators on site. Channels (fields in the database) can be added when new equipment comes on site, removed when equipment fails, and changed when a transmitter is re-assigned. Obviously I won't be removing fields from the database, once they have been used!

The code I used for creating the table in Access was:

Sub CreateTable()
  Dim dbs As Database, tbl As TableDef, fld As Field
  Set dbs = CurrentDb
  Set tbl = dbs.CreateTableDef("Squirrel")
  With tbl
      Set fld = .CreateField("ID", dbLong)
      fld.Attributes = dbAutoIncrField + dbFixedField
      .Fields.Append fld
          .Fields.Append .CreateField("Date/Time", dbDate)
          For i = 1 To 200
              .Fields.Append .CreateField("Chan " & i, dbDouble)
          Next i
  End With
  dbs.TableDefs.Append tbl
  dbs.TableDefs.Refresh
  End Sub

We currently only need 146 fields. I created 202 just to allow for some future expansion (max 250 channels on the Squirrel unit). I’d rather not have lots of unnecessary unused blank fields.

 

[SIXTH SENSE]

I'm using Excel 2007 & Access 2007.

I'm having a problem getting Excel to report the correct number of fields for an Access table. Days of googling and reading haven't provided with a solution that works!

When using the (unfinished) Addcolumn sub below in Excel, the MsgBox results are 11 Records and 9 fields (I commented out the alter table code while testing). When I run this code in the Access immediate window:

msgbox currentproject.Connection.Execute("Squirrel", , adCmdTable).Fields.Count

The above code gives me the correct results, Which is currently 202 fields (key, Date/Time, Temperature or voltage × 200 fields).

I noticed that in my sub it doesn't refer to the table "Squirrel", is this where I'm going wrong?

What I'm trying to do is count the number of fields in the database. Then I need to test whether the CSV data I have processed in Excel has more columns than the fields currently in the database. If so, then I will create new fields to allow the data to be pushed to the Access database.

I'll also need to test that the data I'm adding isn't older than the last piece of data in the table, but I'll cross that bridge later!

Sub AddColumntoSquig()
      Dim conn As New ADODB.Connection
      Dim StrNewColumn As String
      Dim rsT As ADODB.Recordset
      Dim intTblCnt As Integer, intTblFlds As Integer
      StrNewColumn = "ALTER TABLE Squirrel Add [Chan 201] DEC;"
     With conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .CursorLocation = adUseClient
        .Open "Data Source=C:\Documents and Settings\xxxx\My Documents\My Data Sources\Test1.accdb"
        .Execute StrNewColumn
      End With
     Set rsT = conn.OpenSchema(adSchemaTables)
      intTblCnt = rsT.RecordCount
      intTblFlds = rsT.Fields.Count
      MsgBox ("Records: " & intTblCnt & ";" & vbCrLf & "Fields: " & intTblFlds)
     conn.Close
     Set conn = Nothing
  End Sub

FIO: "Squirrel" refers to our "Eltek Squirrel Telemetric Temperature Monitoring System & Automated Alarm Callout System". It monitors all of our Fridges, Freezers and Incubators on site. Channels (fields in the database) can be added when new equipment comes on site, removed when equipment fails, and changed when a transmitter is re-assigned. Obviously I won't be removing fields from the database, once they have been used!

The code I used for creating the table in Access was:

Sub CreateTable()
  Dim dbs As Database, tbl As TableDef, fld As Field
  Set dbs = CurrentDb
  Set tbl = dbs.CreateTableDef("Squirrel")
  With tbl
      Set fld = .CreateField("ID", dbLong)
      fld.Attributes = dbAutoIncrField + dbFixedField
      .Fields.Append fld
          .Fields.Append .CreateField("Date/Time", dbDate)
          For i = 1 To 200
              .Fields.Append .CreateField("Chan " & i, dbDouble)
          Next i
  End With
  dbs.TableDefs.Append tbl
  dbs.TableDefs.Refresh
  End Sub

We currently only need 146 fields. I created 202 just to allow for some future expansion (max 250 channels on the Squirrel unit). I’d rather not have lots of unnecessary unused blank fields.

hi,

you could try issuing an sql command such as

sql = "SELECT * FROM table where crit1 limit 1"
Set rs = cn.Execute(sql)


MsgBox rs.Fields.Count

this will return the number of field return by query

 

[placebo2000]

Many thanks, so simple and works like a charm!

 

[SIXTH SENSE]

Many thanks, so simple and works like a charm!

great!