Field Not Updateable until I Refresh Links in linked table Manager 1

[BigOrangeMonkey]

I am generating linked tables via VBA, I then launch an update query to select data from another access database into an SQL database.

When I run the query I get the message "field not updatable", If I then go into the Linked Table Manager and simply refresh the external linked tables the queries will run.

Any Ideas?

Thanks,
Steve.

Here is the code I use to generate the links :

Sub CreateLinkedExternalTable(strTargetDB As String, _
         strProviderString As String, _
         strSourceTbl As String, _
         strLinkTblName As String)

   Dim catdb As ADOX.Catalog
   Dim tblLink As ADOX.Table
   Dim strsql As String

   Set catdb = New ADOX.Catalog
   ' Open a Catalog on the database in which to create the link.
   catdb.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strTargetDB

   Set tblLink = New ADOX.Table
   With tblLink
      ' Name the new Table and set its ParentCatalog property to the
      ' open Catalog to allow access to the Properties collection.
      .Name = strLinkTblName
      Set .ParentCatalog = catdb

      ' Set the properties to create the link.
      .Properties("Jet OLEDB:Create Link") = True
      .Properties("Jet OLEDB:Link Provider String") = strProviderString
      .Properties("Jet OLEDB:Remote Table Name") = strSourceTbl
      .Properties("Jet OLEDB:Cache Link Name/Password") = True
   End With

   'Append the table to the Tables collection.
   catdb.Tables.Append tblLink

   Set catdb = Nothing
End Sub

 

[hermanlaksko]

A table in an SQL database will need to have a unique field for each record, could that be it?.

Also you code for linking looks odd, maby thats just me ;-) , som I incl. my code for linking odbc (sql-server)tables:

Public Function AttachTableSQL(AttDatabase, AttTable)
Dim MyDB As Database
Dim MyTable As TableDef
Dim MyField As Field
Dim FlexTable
On Error GoTo Err_AttachTable

Set MyDB = CurrentDb
On Error Resume Next
FlexTable = AttTable
If Left(AttDatabase, 4) = "ODBC" Then
If IsTable(FlexTable) Then
MyDB.TableDefs.Delete FlexTable
End If
Set MyTable = MyDB.CreateTableDef(AttTable, dbAttachSavePWD, AttTable, AttDatabase & "dbo." & AttTable)
MyDB.TableDefs.Append MyTable
'DoCmd.Rename FlexTable, acTable, AttTable
End If

Exit_AttachTable:
Exit Function

Err_AttachTable:
If Err <> 3265 Then MsgBox Err.Description
Resume Exit_AttachTable
End Function

Let me know how it turnes out

 

[BigOrangeMonkey]

Every table has a unique clustered index.

To explain my app further I have an Access database full of data in many tables and is my data source, I also have SQL and Oracle Databases which are my data targets.

My access app sits in the middle. This code works fine on Windows 2000 sp2 but fails on windows 2000 sp1 and windows 98se.

On all operating systems that fail I can go into the Linked table Manager, hit refresh links and then the queries are ok.

I cannot set static links to the Source file as its name changes with every new release and the target databases can also move about, ie, different customers, live and test databases etc.

 

[hermanlaksko]

Lively bunch ;-)

Did you tru my link function?

 

[BigOrangeMonkey]

Im working out what its up to first Thanks

 

[BigOrangeMonkey]

It works, great stuff

Now I just need to work out why to satisfy my curiousity, but that can wait

Thanks