Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 881
  • Last Modified:

VBA ODBC linked tables not updatable - MS Access

VBA MS Access 2007

When I use this code, the linked tables records are not editable, when I link the tables manually for each table the unique identifier is asked and after that the table records are editable.

I need to link them via code, how can I solve this, tried two different ways to link them, so i need to set the unique identifier through code

TblNaam = tablename
MdbNaam = "ODBC;DSN=SQLPS;UID=sa;PWD=password" (ODBC is a System DSN)

First method used (preferred method)
                    Set tbfNewAttached = LocalDb.CreateTableDef(TblNaam)
                    With tbfNewAttached
                        .Connect = ";database=" & MdbNaam
                        .SourceTableName = TblNaam
                    End With
                    LocalDb.TableDefs.Append tbfNewAttached
                    Set tbfNewAttached = Nothing

Open in new window


Second method tried:
DoCmd.TransferDatabase acLink, "ODBC Database", MdbNaam, acTable, "dbo." & TblNaam, TblNaam

Open in new window

0
IT-Factory
Asked:
IT-Factory
  • 5
  • 4
1 Solution
 
RemRemRemCommented:
It appears as if you do not have primary keys set on the table you are linking to. If you go to the table itself and set one, when you link programmatically, the ODBC connection will see that key and use it. The reason it doesn't like the link otherwise is that in order to be editable via Access, it needs uniqueness.

If you don't otherwise have a PKey in the table, you could just create a "JunkID" field and set it to auto populate, ignoring it for everything other than this link/edit feature.

-Rachel
0
 
IT-FactoryAuthor Commented:
An identity column is present so I do not think that is the problem???
Column:
Name: ID
Is Identity: yes
Identity increment: 1
Identity seed: 1
0
 
IT-FactoryAuthor Commented:
anyone an idea...I'm stuck.
A solution is to link the tables manually, but each time I open the Access file he asks for the SQL password again, even when told to save the password...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RemRemRemCommented:
Sorry - missed the first request for additional ideas.

It's a long shot, but is there any chance the data in your table isn't de-duped despite having an identity column (or has nulls trapped in it)? I've seen this happen in merged conversion data sets on rare occasions. To resolve it, you'd want to do a query against it on the SQL server looking for essentially:

Select ID, count(ID) from <tblName> where count(ID)>1

If you find duplicates, try correcting it to unique values and then seeing if it relinks without grief.

-Rachel
0
 
IT-FactoryAuthor Commented:
Tables are empty, newly created on the sql server...??? Ready to be used...but I can't because they are read only...
0
 
RemRemRemCommented:
Hmm. Can you force two fake records in on the SQL server, which should then show up with unique IDs, and see if that makes a difference for the linking?

I'm really grabbing at straws for you at this point, so I totally understand if you don't want to try more ideas from me. Hopefully someone else can pipe up with resolutions!

-Rachel
0
 
IT-FactoryAuthor Commented:
found solution added this line of code after LocalDb.TableDefs.Append tbfNewAttached

docmd.runsql "CREATE UNIQUE INDEX idx" & TblNaam & " on " & TblNaam & "(" & KeyField & ");"

Open in new window


Where KeyField is the ID field of the table.
Needed an extra parameter but this seems to solve the problem, force the index.
0
 
RemRemRemCommented:
Awesome! Thanks for sharing it!

-Rachel
0
 
IT-FactoryAuthor Commented:
found solution
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now