Add a field to a table in another database (1 Viewer)

[suepowell]

Hi All.

I know what I want to do shows bad database design, but we can't get it right all of the time.

I have a database with front end / back end which I have designed and deployed to multiple sites.

One of my users has asked for some extra functionality not in the original spec (a common occurance once new users see what might be possible) , and this needs a new field in an existing table. This functionality would be usefull o others so I want to deploy it to all my users.

I want to be able to write some code to add this field to the table so I can deploy the change by sending my users a small database with a form with a button to run this code, without me having to visit each site.

I can easily deploy the new frontends, but need a way of updateing the back ends remotely as I hope to deploy this to many more sites over a large geographic area.

I am sure as my softeware gets out there there will be more enhancement requests for more people so I want to devise the method for achieving this as easily as possible.

Thanks for your help.

Sue

 

[pono1]

A place to start...

Private Sub CommandGo_Click()

  ' Get the path to the access file.
  Dim DBpath As String
  DBpath = "\\ComputerName\ShareName\databaseName.mdb"
  ' Create a connection string using the path.
  Dim Connect_String As String
  Connect_String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & DBpath
  ' Connect to the database using the connection string.
  Dim adoConnect As New ADODB.Connection
  Call adoConnect.Open(Connect_String)
  ' Add a column to a table in the database.
  Dim mySQL As String
  mySQL = "ALTER TABLE tbl_name ADD first_name text"
  adoConnect.Execute mySQL
  ' Clean up.
  adoConnect.Close
  Set adoConnect = Nothing

End Sub

Regards,
Tim

 

[gemma-the-husky]

i gave up in the end, as i found it difficult to set some new field characteristics in the remote database

what i have built instead is a utility to store all the field definitions, and index definitons based on my home development system

then on a users system it checks and identifies any new fields/indexes, and incorrect settings, to fix manually

 

[suepowell]

Hi Both thanks for the replys.

Tim, I'll have a go and see if I can get it to work for me, I'm using access 2007 so I think I should be using DAO rather than ADO but the priciples should be very similar.

Gemma, your approach won't work for me as some of my databases are not easy to visit, bit I am very interested in the utility you have built to compare database tables.

I have been trying to buy something like this but the one I have found isn't available for accress 2007 yet.

I am also looking for a product that allows me to compare all aspects of a database, tables, code, forma controls etc, but again the only comapny I have found who do tis havn't got the access 2007 version out yet, so if you know of a suitable product please let me know.

Thanks again for your replies.

Sue

 

[LPurvis]

ADO is still an option for you - but if the database in in ACCDB format then you just won't be able to use the Jet OLEDB provider, but the Access 12 one instead.
i.e.
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBpath

DAO opening of the external database is an option for you - and is generally easier (shorter) to knock out.
Set db = OpenDatabase(strDBpath)
db.Execute "ALTER TABLE tblTableName ADD COLUMN YourNewColumn Text(50)"

Bearing in mind that the OLEDB providers generally expose slightly more DDL functionality than DAO provides.


However - you don't need to establish an external connection first at all.
Using the Current Jet instance and an external DDL statement alone:

CurrentDb.Execute "ALTER TABLE [" & strDBpath & "].tblTableName ADD COLUMN YourNewColumn Text(50)"

Single statement and done.
(FWIW if I were doing this, especially repeatedly as part of a BE update, I'd usually open a database or connection - but for a single hit doesn't make much difference).

 

[pono1]

I like the one-liner... My guess is Sue will probably be spending much of her time on exception handling for this effort...

 

[boblarson]

Actually, I have a tool that lets you do remote table changes (add/delete fields, add/delete tables, etc.) It is called the Backend Auto Update Tool and it is located here:
http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx

 

[boblarson]

And a bit more about the tool - it is set so you can send it to someone and if they open it, it will automatically go do what it needs to do (based on entries you've made in the table) and then it shuts itself. We had to do something up for a client I worked with and so that's what I came up with.

 

[suepowell]

Hi

I have used Lpurvis's suggestions to great effect, very easy and straightforward so thanks for that.

Just one more question, is it possible to use this to change a column name, and if so do you know what the command is.

Thanks again very helpful.

Sue

 

[LPurvis]

No, you can't use DDL statements to change the name of a field.
You have to use code (DAO or ADOX - and naturally DAO is slightly simpler in implementation for Jet dbs).

However - as your question was about performing changes on a remote database for application updates, I'd recommend against changing field names.
Adding new ones, certainly. Deleting old ones - if you really feel it's necessary.

Renaming could be a minefield - especially remotely.