Modifying and adding tables to existing database 1

[JR2913]

Hi

Having just released a database (split front/backend) the powers that be have decided to change the rules for data stored in one of the fields in a large table from numeric to text, with no direct equivalent between the numeric and text values (really helpful, eh?). I need to change the data type for this field - it would be possible to assign a temporary conversion between the numeric and text values in use so that users could edit these at a later time to ensure accuracy.

In addition I have a feeling that it is going to be necessary to add tables to the back-end database that is already in use because of additional information that is soon going to be required.

Is there a quick and effective way of carrying out these changes, seemlessly to the user, when a revised front end is provided, so that data in the back end is not lost?

Any advice gratefully received.

Thanks in advance

JR

 

[KenReay]

Hi

When you say 'released' do you mean sent to someone/some company at a remote location?

If no, why not just make the changes to the BE via the Access Interface?, you can add new tables etc as without disrupting users, since these will not be in use until you release the Front End which uses them, changes to existing tables will have to be done out of hours or during scheduled down time, since you cannot amend a table structure when a table is open by another user.

If your problem is that the BE database is remote from you, it is possible to write code to manipulate the structure of the data base. This can be done either by acting upon the collections which hold the structure (eg Tabledef, Fields, etc) or by using DDL (Data Definition Language) SQL statements. New releases of the FE could contain the routines to do this, but you would need a mechanism to recognise when it had been actioned so as not to repeat it every time the FE ran. This is not too difficult with a bit of thought, eg say you ahve to add a new table, test for the presence of the table, if not present, add it, if present do nothing.

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JR2913]

Thanks, Ken.

Definitely remote - >50 locations nationwide.

How would I add a new field, for example, to an existing table without affecting the current data?


JR

 

[KenReay]

Hi

Which version of Access are you using, to be specific, are you using ADO or DAO as data access method?

How familiar are you with VBA code? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JR2913]

Ken

Although I'm using Offixe XP Developer, the application is designed for Access 2000, so using DAO.

I have used VBA in Excel, but not so much in Access - treat me as a novice!

John R

 

[KenReay]

Hi

Well here is a bit of code that will open a BE database and add a given field to a table within that database.

I think it is understandable.

There are of course more properties you can set for the field (eg size etc), but I am sure you will get the idea, plus if you look in help, under the various objects (eg Tabledefs, Fields etc the info is all there

If you are stuck, EMail me on kenreay@naafi.co.uk until 14:30 UK time, after that kenneth.reay@talk21.com and I will try to answer.

It is possible to do the same kind of thing using SQL, but I have not actulaay done that before... Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JR2913]

Thanks Ken, but can't se the code you mention......

Have tried emailing you but doesn't recognise your address - bounced back as undeliverable.

John R

 

[KenReay]

Hi

Sorry......... forgot to paste it

Public Sub AddField()
Dim Ws As Workspace
Dim Db As DAO.Database
Dim tdf As TableDef
Dim dld As Field
'
Set Ws = DBEngine.Workspaces(0)

Set Db = Ws.OpenDatabase("C:\documents and settings\kreay\my documents\KenBE.mdb&quot
Set tdf = Db.TableDefs("tblSupplier&quot
Set Fld = New Field
Fld.Name = "Address1"
Fld.Type = dbText
tdf.Fields.Append Fld
Set Fld = Nothing
Set tdf = Nothing
Set Db = Nothing
Set Ws = Nothing
End Sub

EMail Addresses should be OK,
kreay@naafi.co.uk or kenneth.reay@talk21.com

Not a good morning, Christams PArty last night, and I am the most sober one in the office! Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JR2913]

Thanks, Ken.

I'll give this a go, but probably not today........

Hope you didn't drive to work!


Regards

John R

 

[JR2913]

OK Ken, thanks for that - works fine. Will it be possible to add a new field if the table already has relationships with other tables?

Now.......

How can I......

(a) check to see if the field already exists in the table?

(b) add a new table to the BE database?

(c) create a link between, for example, the UserID in the new table and UserID in an existing table?


Very grateful for your help with this....

 

[KenReay]

Hi

Not sure I know how to do all the things you ask, but:

a) to check if field exists reference it by name in the tabledef (eg tdf.Fields("MyName&quot) and trap the resulting error if its not found

b) Not sure how to do this have been experimenting with append method of tabledefs collection, but no luck so far, you could also consider a DDL SQL statement. I will keep trying

c) Again not sure how to do this, but it must be possible, I will have a little experiment this afternoon Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

HI

To add a table

Dim Db As Database
Dim Tdf As TableDef
Dim tdfs As TableDefs
'
Set Db = OpenDatabase("C:\KenReay\db2.mdb&quot
Set tdfs = Db.TableDefs
Set Tdf = Db.CreateTableDef("KensTable&quot
'
Tdf.Fields.Append Tdf.CreateField("KensField", 2, 50)
tdfs.Append Tdf
tdfs.Refresh Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK