Re: defining keys and relationships in VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Robb (anonymous_at_discussions.microsoft.com)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 13:54:41 -0700

It's close on the first step. I keep getting a type
mismatch error. Here's the code:

Function UpdateData()

Dim dbs As Database, tdf As TableDef
Dim idx As Index
Dim fldWONUM As Field, fldWoLine As Field, fldOperation As
Field

DoCmd.TransferSpreadsheet
acImport, , "UpdateData", "C:\Documents and
Settings\brunanskyr\My Documents\UpdateData.xls", True
DoCmd.OpenTable "UpdateData", acViewDesign, acEdit

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!UpdateData
Set idx = tdf.CreateIndex("PrimaryKey")
Set fldWONUM = idx.CreateField("WO NUM", dbText) <<<<THE
ERROR OCCURS AT THIS STEP>>>>
Set fldWoLine = idx.CreateField("Wo Line", dbText)
Set fldOperation = idx.CreateField("Operation", dbText)

idx.Fields.Append fldWONUM
idx.Fields.Append fldWoLine
idx.Fields.Append fldOperation

idx.Primary = True

tdf.Indexes.Append idx
tdf.Indexes.Refresh
Set dbs = Nothing

End Function

Any ideas? Thanks

>-----Original Message-----
>Assuming you've got a reference set to DAO, a couple of
changes to the
>following from the Help file should do the first step
(creating the Primary
>Key):
>
>Sub NewIndex()
> Dim dbs As Database, tdf As TableDef
> Dim idx As Index
> Dim fldLastName As Field, fldFirstName As Field
>
> ' Return reference to current database.
> Set dbs = CurrentDb
> ' Return reference to Employees table.
> Set tdf = dbs.TableDefs!Employees
> ' Return reference to new index.
> Set idx = tdf.CreateIndex("FullName")
> ' Create and append index fields.
> Set fldLastName = idx.CreateField("LastName", dbText)
> Set fldFirstName = idx.CreateField("FirstName", dbText)
>
>idx.Fields.Append fldLastName
> idx.Fields.Append fldFirstName
> ' Append Index object and refresh collection.
> tdf.Indexes.Append idx
> tdf.Indexes.Refresh
> Set dbs = Nothing
>End Sub
>
>The changes are name the index PrimaryKey, rather than
FullName (actually,
>this isn't actually mandatory) and add the line
>
> idx.Primary = True
>
>before you append idx to the tdf object.
>
>To create a relationship in code, this example (also from
the Help file)
>should do it:
>
>Sub NewRelation()
> Dim dbs As Database, rel As Relation, fld As Field
>
> ' Return reference to current database.
> Set dbs = CurrentDb
> ' Create new Relation object and specify foreign table.
> Set rel = dbs.CreateRelation
("CategoryProducts", "Categories", "Products")
> ' Set attributes to enforce referential integrity.
> rel.Attributes = dbRelationUpdateCascade And
dbRelationDeleteCascade
> ' Create field in Relation object.
> Set fld = rel.CreateField("CategoryID")
>
>' Specify field name in foreign table.
> fld.ForeignName = "CategoryID"
> ' Append Field object to Fields collection of Relation
object.
> rel.Fields.Append fld
> ' Append Relation object to Relations collection.
> dbs.Relations.Append rel
> dbs.Relations.Refresh
> Set dbs = Nothing
>End Sub
>
>In the second example, if you've also got a reference set
to ADO, you should
>change the declaration from fld As Field to fld As
DAO.Field, since both the
>DAO and ADO models have a Field object in them, and you
want to ensure that
>you get the correct one.
>
>--
>Doug Steele, Microsoft Access MVP
>http://I.Am/DougSteele
>(no e-mails, please!)
>
>
>
>"Robb" <anonymous@discussions.microsoft.com> wrote in
message
>news:5aa901c491e3$ef5b2c70$a501280a@phx.gbl...
>> I have a procedure that imports an Excel spreadsheet as
a
>> new table into my Access database. Once I get the table
>> imported, I manually have to set the primary key (which
>> consists of three fields) and define the one-to-one
>> relationship with cascade update between the new table
and
>> the db table.
>>
>> Once that is done, I have a procedure that runs an
update
>> query to update the db table with the imported data
(which
>> comes from an oracle database) and another query that
asks
>> the user to give a user-defined date and then updates a
>> field in a subset of the db table with that date. Then
the
>> procedure deletes the imported table since it is no
longer
>> needed.
>>
>> I would like to automate the whole process. Is there a
way
>> to define a primary key (that consists of three fields)
in
>> a table and then create relationships and enforce ref
>> integrity in VBA, or will this always be a manual
>> operation? Thanks for any help!
>>
>> Robb
>
>
>.
>

Relevant Pages

  • Re: Who is logged in?
    ... that will show who is currently "logged in" to the database. ... Dim recSet As New ADODB.Recordset ... Dim idx As Long ... ' The user roster is exposed as a provider-specific schema rowset ...
    (microsoft.public.access.security)
  • Re: Who is logged in?
    ... (no private e-mails, please) ... that will show who is currently "logged in" to the database. ... Dim recSet As New ADODB.Recordset ... Dim idx As Long ...
    (microsoft.public.access.security)
  • Re: Who is logged in?
    ... Doug Steele, Microsoft Access MVP ... that will show who is currently "logged in" to the database. ... Dim recSet As New ADODB.Recordset ... Dim idx As Long ...
    (microsoft.public.access.security)
  • RE: Who is logged in?
    ... One may use the Jet User Roster for checking users logged into the database. ... Dim recSet As New ADODB.Recordset ... Dim idx As Long ...
    (microsoft.public.access.security)
  • Re: Who is logged in?
    ... that will show who is currently "logged in" to the database. ... Dim recSet As New ADODB.Recordset ... Dim idx As Long ... ' The user roster is exposed as a provider-specific schema rowset ...
    (microsoft.public.access.security)