August 2, 2002 at 1:25 pm
Hello,
I am linking a view from SQL Server 2000 through VBA code in Access. In order to allow updates I need to indicate the Primary Key. I am using the following, but I get an error telling me that there are too many indexes defined after I append the table, which is not true. I think I am not setting the index properly.
Can anyone help??
Thanks,
Dan
Here is the code:
CurrentDb.TableDefs.Delete (strTable)
If err.number Then
Debug.Print err.Description
err.Clear
End If
Set tdfLinked = CurrentDb.CreateTableDef(strTable)
If strTable = "v_PivotRebates" Then
Set idxPrimary = tdfLinked.CreateIndex("PrimaryKey")
idxPrimary.Fields.Append tdfLinked.CreateField("ProductInternalNbr")
tdfLinked.Indexes.Append idxPrimary
End If
tdfLinked.Connect = strconnect
tdfLinked.SourceTableName = strSourceTable
CurrentDb.TableDefs.Append tdfLinked
August 2, 2002 at 2:12 pm
Been a while since I used Access, do remember part of this - even though the view is to a table that has a primary key Access doesn't pick it up when you link, you have to tell Access what the primary key is. I'd suggest doing it manually once, then write some code to dump out the properties. I suspect Access might not be adding a true index but just marking which col(s) comprise the pkey.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply