ALTER sequence counter on LINKED Tables

[Dave_D]

I recently switched 2 data table to BE. Prior to doing this, I was issuing the following



CurrentDb.Execute "ALTER TABLE tablename ALTER COLUMN Sequence Counter (1,1)"

Which worked perfectly fine, but after making the switch to BE my code became

SET appAccess = OpenCurrentDb("C:\ ...." , FALSE)
Msgbox Err.Number - returns 0
appAccess.RUN "Altercol" tablename

In the BE DB, I placed the CurrentDb.Execute "ALTER....." statement in a MODULE, but I get a runtime error 438 on appAccess.RUN.

1. Does appAccess become my current DB?
2 Is this acceptable to alter a linked table?
3 What would be the proper syntax to perform the ALTER? Is it merely writing an SQL statement and them executing that stateement?

[ranman256]

you cannot alter linked tables in the FE.
you must be IN the BE to alter them. But why alter them in code? when you can just edit the new field.

[pbaldy]

I know you can do it from outside the BE because I've sent utility db's to clients to update back end tables. I'll try to dig up the code. I can't remember if I used DAO or SQL.

[Dave_D]

you cannot alter linked tables in the FE.
you must be IN the BE to alter them. But why alter them in code? when you can just edit the new field.

When I issue the OpenDataBase(C:\....) I am opening the BE to allow the sequencing to renumber. So if the BE is opened, shouldn't I be able to ALTER those tables by using either SQL or DAO?

[pbaldy]

Found it. I used SQL. Your issue is you're not using the db variable to execute the SQL. Try

appAccess.Execute "ALTER TABLE...", dbFailOnError

[Dave_D]

Found it. I used SQL. Your issue is you're not using the db variable to execute the SQL. Try

appAccess.Execute "ALTER TABLE...", dbFailOnError

Paul,
That worked beautifully! Thank you. So maybe you can resolve my second issue now, where I get a runtime error 3295 - Syntax error in DROP TABLE or DROP INDEX.

Set db = Currentdb
For Each tbldef in db.Tabledef
If tbldef.name = "tablename" Then
db.execute "DROP TABLE" & tbldef.name, dbFailOnError ----> Runtime error 3295
End if
Next tbldef

[pbaldy]

For starters, without a space after TABLE that will concatenate to:

db.execute "DROP TABLEtablename

which the engine probably won't be able to resolve.

[Dave_D]

For starters, without a space after TABLE that will concatenate to:

db.execute "DROP TABLEtablename

which the engine probably won't be able to resolve.

I got it resolved. There was a space, just can't type correctly, but I needed to put brackets around my table name because of the way it was named.

[pbaldy]

Glad you got it sorted. Brackets would have been next on my list.