How to determine key fields in a table in VBA?

[StuckInTheMiddle]

I'm trying to create a generic function in VBA that allows me to update/insert to any Access table (using ADO). In doing this i need to determine a tables key fields.

Is there a way to programmatically determine the key fields in an Access table through VBA?

A,

 

[hwkranger]

Yes. look at the schema and find the primary key. Tada.

The following prints to the debug window. You can modify.

Dim rs As ADODB.RecordSet
  Set rs = CurrentProject.Connection.OpenSchema( _
          adSchemaPrimaryKeys, Array(Empty, Empty, "YOUR_TABLE_NAME_HERE"))
  Do While Not rs.EOF
    Debug.Print rs(3)
    rs.MoveNext
  Loop
  rs.Close

Randall Vollen
National City Bank Corp.

 

[PHV]

Why using ADO for an access table as Microsoft itself says that DAO is much faster for Jet databases ?
get1stFieldOfPK = CurrentDb.TableDefs(strTable).Indexes("PrimaryKey").Fields(0).Name


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Does Microsoft really say that DAO is much faster for Jet databases? For everything?

Try the following experiments - here tweaking hwkrangers code to match PHV's:

[tt]' in the declaration section
Private Declare Function QueryPerformanceCounter Lib "Kernel32" _
(lpPerformanceCount As Currency) As Boolean


private sub testdao
dim s as string
dim c as currency
dim c2 as currency

if QueryPerformanceCounter(c) then
s = CurrentDb.TableDefs(strTable).Indexes("PrimaryKey").Fields(0).Name
QueryPerformanceCounter c2
debug.print "DAO version: ", c2-c, s
end if
End Sub

private sub testado
dim s as string
dim c as currency
dim c2 as currency

if QueryPerformanceCounter(c) then
s = CurrentProject.Connection.OpenSchema( _
adSchemaPrimaryKeys, Array(Empty, Empty, _
"YOUR_TABLE_NAME_HERE")).Fields(3).Value
QueryPerformanceCounter c2
debug.print "DAO version: ", c2-c, s
end if
End Sub[/tt]

On my setup, the ADO version is on average 30% faster.

For more info on this timer, have a look at

http://support.microsoft.com/default.aspx?scid=kb;EN-US;172338

Roy-Vidar

 

[PHV]

Hi Roy !
My claim about DAO vs ADO wasn't for the PK request, but a reply to this:
update/insert to any Access table (using ADO)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Hi again Roy !
Furthermore I guess that ADODX is much flexible due the lack of many DDL features in the JetSQL implementation.