Add column to table of linked backend

[Vulcan1500]

Due to an unforseen modification in my database, I need to add a column to a table of the linked backend. I do not prefer users to modify this table, but try to do this with code in the frontend of the release that comes with the modification. For me it seems impossible to add a variable as an integer to this table. MS Access returns an error as soon as I try to add the column with db.Execute (strSQL) in the following code:

[FONT=Arial][COLOR=black]Dim db As DAO.Database[/COLOR][/FONT]
[FONT=Arial][COLOR=black]Dim rs As DAO.Recordset[/COLOR][/FONT]
[FONT=Arial][COLOR=black]Dim strSQL As String[/COLOR][/FONT]
[FONT=Arial][COLOR=black]Set db = OpenDatabase("C:\Aalles\My_backend.mdb")[/COLOR][/FONT]
[FONT=Arial][COLOR=black]strSQL = "ALTER TABLE MyTable " & _[/COLOR][/FONT]
[FONT=Arial][COLOR=black]           "ADD COLUMN BackupAantal Integer;"[/COLOR][/FONT]
[FONT=Arial][COLOR=black]db.Execute (strSQL)[/COLOR][/FONT]

MS Access gives error 3211 due to it could not lock MyTable being in use by another person or process.

 

[boblarson]

Unfortunately you won't be able to modify anything in an existing item if someone has a frontend open to the linked backend. You would need to make sure everyone exits out before making the mod.

 

[Vulcan1500]

Thanks for your reaction Bob and due to that I now realise my explanation was not complete. Users of the database are not on a network using the same backend, but have the database on individual computers. The error I get is when I test the frontend linked to the backend. Any idea?

 

[boblarson]

The error I get is when I test the frontend linked to the backend. Any idea?

If you are using this code in a frontend that is linked to the backend, the second you open the frontend, the backend is opened by Access and therefore someone is in it. You can't do it with something that is linked to that backend. It would have to be a completely disconnected database file.

 

[Vulcan1500]

OK Bob, that's why I'm another user. Knowing this an idea could be to break the link with the backend table, add the column and reinstall the link. If this is possible do you know how to break and reinstall the link?

 

[boblarson]

I'm sure there is, but I'm not sure what it would be as I've not gone that direction. You probably will want to post another thread on that with that exact question.

 

[Vulcan1500]

OK Bob, I will do that.

 

[DCrake]

Simple Software Solutions

To break a link between the front end and the back end use the following

DoCmd.DeleteObject acTable, "Your Table Name"


This removes the link but does not delete the source table from the back end.

Now run the code to amend the table

then relink the table

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\Aalles\My_backend.mdb", acTable, "TableName", "TableName", False

CodeMaster:

 

[boblarson]

To break a link between the front end and the back end use the following

DoCmd.DeleteObject acTable, "Your Table Name"


This removes the link but does not delete the source table from the back end.

Now run the code to amend the table

then relink the table

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\Aalles\My_backend.mdb", acTable, "TableName", "TableName", False

CodeMaster:

Hey thanks!

 

[Vulcan1500]

Thanks DCrake for your help!
The backend table is first disconnected and then connected. Perfect. The code I'm using (as given earlier in this thread) to add the column gives me still error 3211. Any idea on this?

 

[boblarson]

Thanks DCrake for your help!
The backend table is first disconnected and then connected. Perfect. The code I'm using (as given earlier in this thread) to add the column gives me still error 3211. Any idea on this?

Have you deleted ALL linked tables first?

 

[Vulcan1500]

No Bob only the link to the table I wanted to alter was disconnected. The only reason where I can now think of is that once the code starts to add the column the backend as such is still in use. This would also be the case when all links are disconnected, isn't it? If that is the reason the total backend must be disconnected. but how?

 

[boblarson]

No, you need to have all of the tables deleted otherwise the database is considered open. But, if your other code doesn't work, I have a backend auto updater that works but you have to make sure all are out of it first.

 

[Vulcan1500]

Eventualy I found my stupid error. I still had the recordset open! Sorry for that!

 

[boblarson]

Hey, as long as you found the problem and got it solved - great!