Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to determine key fields in a table in VBA?

Status
Not open for further replies.

StuckInTheMiddle

Programmer
Mar 3, 2002
269
US
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,
 
Yes. look at the schema and find the primary key. Tada.

The following prints to the debug window. You can modify.
Code:
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.
 
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
 
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
Roy-Vidar
 
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
 
Hi again Roy !
Furthermore I guess that ADODX is much flexible due the lack of many DDL features in the JetSQL implementation.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top