Data Definition Query No-Go with Linked Tables

[ALewis06]

I have a simple data definition query that I use to add a Primary Key to a table. It worked fine when the tables are all local but now that I have split the database and the tables are linked, I get an error message when I try to run the data definition query. Any workaround?

 

[jdraw]

If you are an admin/have privileges to BE, during a maintenance period when there are no users on system, sign on to BE and create the PK.

If you are not admin and don't have privileges, make your case to the dba for the PK.

I'm not sure why people/developers would be using DDL to create the PK. I understand how to do it, but would expect most would go to table design and assign the PK in design mode.

 

[marlan]

I'm not sure why people/developers would be using DDL to create the PK. I understand how to do it, but would expect most would go to table design and assign the PK in design mode.

Well, not DDL, but I have 2 cases: for one client I have the quite a few BEs, by time slice, any modification in the BE is made for all BEs. In an other case, a colleague provides his clients the application in a terminal server. It is more accurate to loop through all BEs, and make the modifications by code.

It worked fine when the tables are all local

Sure, you can't modify a linked table, it is only a link. Your current db will only modify what is in it.

Any workaround?

Providing you have the privileges, DAO:
Use OpenDataBase() and DAO.DataBase object to connect to the BE.
See if you can use Execute on the DAO.DataBase object to run your DDL.
If not, here is code I found once:

Sub PrimaryX()

   Dim dbsNorthwind As Database
   Dim tdfNew As TableDef
   Dim idxNew As Index
   Dim idxLoop As Index
   Dim fldLoop As Field
   Dim prpLoop As Property

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   ' Create and append a new TableDef object to the
   ' TableDefs collection of the Northwind database.
   Set tdfNew = dbsNorthwind.CreateTableDef("NewTable")
   tdfNew.Fields.Append tdfNew.CreateField("NumField", _
      dbLong, 20)
   tdfNew.Fields.Append tdfNew.CreateField("TextField", _
      dbText, 20)
   dbsNorthwind.TableDefs.Append tdfNew

   With tdfNew
      ' Create and append a new Index object to the
      ' Indexes collection of the new TableDef object.
      Set idxNew = .CreateIndex("NumIndex")
      idxNew.Fields.Append idxNew.CreateField("NumField")
      idxNew.Primary = True
      .Indexes.Append idxNew
      Set idxNew = .CreateIndex("TextIndex")
      idxNew.Fields.Append idxNew.CreateField("TextField")
      .Indexes.Append idxNew

      Debug.Print .Indexes.Count & " Indexes in " & _
         .Name & " TableDef"

      ' Enumerate Indexes collection.
      For Each idxLoop In .Indexes

         With idxLoop
            Debug.Print "  " & .Name

            ' Enumerate Fields collection of each Index
            ' object.
            Debug.Print "    Fields"
            For Each fldLoop In .Fields
               Debug.Print "    " & fldLoop.Name
            Next fldLoop

            ' Enumerate Properties collection of each
            ' Index object.
            Debug.Print "    Properties"
            For Each prpLoop In .Properties
               Debug.Print "    " & prpLoop.Name & _
                  " = " & IIf(prpLoop = "", "[empty]", _
                  prpLoop)
            Next prpLoop
         End With

      Next idxLoop

   End With

   dbsNorthwind.TableDefs.Delete tdfNew.Name
   dbsNorthwind.Close

End Sub

 

[ALewis06]

@marlan I am way too new to this; I am not trained in VBA or writing code so your response albeit appreciated is Greek to me.

@jdraw I know that I can just assign a PK in table design mode but when I have 15 queries in this db and I was hoping to add all of them to a macro so that all the data would "refresh" that way. Besides the FE of the db will be going to end users who know even less about Access than I do, so I certainly cannot ask them to add the PK every time.

Maybe I will review all steps and make sure that the PK is absolutely necessary but I believe it is.

 

[jdraw]

End users would never be changing table nor index structures.!

You should research automated Front end routines.

I'm still not clear on why you would be adding a PK to a table in production after release to production.

Normally, you would design the database --tables, relationships and indexes --do thorough testing, release to production.

Front end would be automated to the extent they would check latest front end version with what was on their PC. If same version, continue- no change. If latest version of FE is newer/greater than existing version on PC, an automated routine will copy the latest version to the local PC and make it current.

If you need structural changes to the backend, you have a maintenance time with no users on system, make the change to production backend. If ther eis a corresponding change to FE, you change the latest version and put it on a shared drive. When user starts his/her front end, the automated version compare occurs and if needed, the new FE is copied to their PC.

Do some googling--automate front end updates.

 

[ALewis06]

The table is an import from Salesforce.com that has too many blanks in the account ID field so I need to create a unique identifier so that in the event of discrepancies (and there have been some already), I can use the SFDC ID field to pinpoint a particular row of data.

I'll Google....

thx

 

[jdraw]

Or you can possibly do a custom import where you check/validate field for acceptable values.
Anyway you are closer to the current processes and requirements than any reader, so you know priorities.

In may shops database administration is more disciplined than what you are describing.
Good luck with your project.