Adding to Linkes Tables 4

[joebb3]

I have an Access 2K project that has been in steady development for a while now... Here is the low down.

Its a Front End that contains all the coding, forms reports and queries... The Backend contains the data. I did this so that my users can upgrade easily without risk to their data. My problem is that I need to add tables and fields to the backend and I need an easy way to do it so that the user doesn't have to have too much knowledge.

Is there a way to create tables and fields in the backend from the front end. IOW Can you create tables and fields in a linked database.

This is a straight Access DB using the linked Table manager to link the front and backend.

I will answer any questions that you need to know to help.

Thank you,
Joe

 

[1DMF]

We use the same method, MS SQL backend and MS Access frontend.

As I develop I have to create tables via SQL Enterprise manager and then link in the new tables.

As I understand if you issued a create table within VBA it will make a local table in the Access.

It might be possible to connect to the DB programatically and then issue the create table command via the connection.

Is there a reason you can't create the tables in SQL, link them into your Access MDB and then re-issue the application to everyone.

I have everyone using a single version on their personal network drive(Z), When i need to releases fixes/updates etc.. I issue a global net send to log out, and wrote a batch program to copy the master application to everones network drive.

We are a small firm with only 10 employees, so this process takes a few minutes.

What's your environment.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[stix4t2]

What kind of tables do you want to create in the backend? Why do you want to use code to do this?

 

[ddiamond]

I've never tried putting DDL in a pass-thru query, but that might allow you to add fields to your backend database (assuming the user has privileges to do so). You would then need to drop and re-add your linked table from code. If you want, I can show you a code example from Access 97 to link a table, but I don't know if this code will work on Access 2k, nor am I sure if this is what you are trying to do.

 

[joebb3]

Amplifying remarks.

The front end is the application and the data is in the backend. Both are Access MDBs.

The front and backends are sent to the users in various states. They use the "program" to track training at their site. So when I update, I cannot just update ONE backend with the new tables and fields.. I have to find a way to programatically "Upgrade" the backends when I issue a new front end to the field. (Each sites data is unique, that's why I set up the data in a different file).

I just want to create standard data tables. Or I just want to add fields to existing tables. the two halves of the programs are already linked at each particular site... I just can't seem to get there from here... Its frustrating.

Here's an idea... If I Issue an update MDB with the upgrade and this Update.mdb has the complete updated data structure of the backend... Is there a way to compare the Original Backend Data structure with the Updated Structure MDB and then create the elements that don't match? (I would put the "upgrade" code in the backend. I would just have to contact all the sites and walk them through it the first time)

If I could just get by this problem, my app would be so much easier to upgrade!

Thanks for listening!!!
Joe
PS... I don't have (and my users don't have) access to use anything other than the controls that are included in Access... MySQL or any other version is not an option for me. Basically I'm stuck WITHIN Access 2K. If you know anything about the Navy and Marine Corp Intranet (NMCI), you'll understand my pain.

 

[ddiamond]

Joe,

Here is an example from Access 97 where a front end database (db1.mdb) adds a table to a backend database (db2.mdb) and another example where the front end database adds a column. I hope this example applies to Access 2k, but I don't have Access 2k to test with.

Sub CreateTable()
  Dim db As DAO.Database
  Dim sDDL As String
  
  Set db = OpenDatabase("g:\db2.mdb")
  sDDL = "CREATE TABLE TEST (field1 text, field2 text);"
  db.Execute sDDL
  db.Close
  Set db = Nothing
End Sub

Sub AlterTable()
  Dim db As DAO.Database
  Dim sDDL As String
  
  Set db = OpenDatabase("g:\db2.mdb")
  sDDL = "ALTER TABLE TEST ADD column field3 text;"
  db.Execute sDDL
  db.Close
  Set db = Nothing
End Sub

The only tricky part is locking issues. If any user of the front-end app is accessing the table you want to alter, the alter statement will fail. The safest thing to do is to have the local admin get everyone out of the front end app before upgrading. Also, sometimes access holds onto fantom locks. If this happens, you will need to reboot.

 

[ddiamond]

If you create a new table in your backend database using the above code, you will then need to link it to your frontend database:

Sub LinkTable()
  Dim td As DAO.TableDef
  Dim tds As DAO.TableDefs
  
  Set tds = CurrentDb.TableDefs
  Set td = CurrentDb.CreateTableDef("TEST")
  td.SourceTableName = "TEST"
  td.Connect = ";DATABASE=g:\db2.mdb"
  tds.Append td
  tds.Refresh
  
  Set td = Nothing
  Set tds = Nothing
End Sub

 

[1DMF]

nice one diamond have a star

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[joebb3]

That's the ticket!!!! Thanks so much!

I haven't tried it yet, but you managed to take all my rambling and condense them into one succinct post!

I DO have a question. Where you have the path... Can it be global? ie... db2.mdb instead of g:/db2.mdb??? Its not a big deal if you can't, I think I can pull the path from the existing tabledef.

You guys rock!
Joe

 

[JerryKlmns]

joebb3

A nice place to start reading for DDL

 

[ddiamond]

Joe,

I don't know if the path can be global. I've always provided an actual path. If your backend database is in the same path as your front end database, you can extract the path from currentdb.name. I've often used the following code:

Public Function Path_Of_DB() As String
  Dim sDBName As String
  Dim Length As Long
  Dim i As Long
  
  sDBName = CurrentDb.Name
  Length = Len(sDBName)
  
  For i = Length To 1 Step -1
    If Mid$(sDBName, i, 1) = "\" Then Exit For
  Next
  
  Path_Of_DB = Mid$(sDBName, 1, i)
End Function

 

[ddiamond]

Jerry,

That was a nice link. I was surprised to see that Access 2000 supports the "DROP COLUMN" clause. That is a feature I've wished for for a long time, but neither DB2 or Oracle has it (those are the databases my company uses).

- Dan

 

[JerryKlmns]

And also to mention that

CurrentProject.Path & "\" & CurrentProject.Name = CurrentProject.FullName

And Dan, scroll down that link to read the next level.
Thnx 4 the *

 

[joebb3]

Thank you so much everyone! It worked like a CHARM!!!!!

Awesome info!
Joe