Solved

Updating a linked table from front end

Posted on 2006-06-17
14
380 Views
Last Modified: 2008-03-29
Hi experts

I have a front end that is a mde database and the back end is linked to a network drive (J:)
What I want to do is make the front end update the backend mdb database, the only change is a New field added to a table.
What I have to do at present is get them to email me there backend, I make the change then email back again. Would be alot easier if I can update from front end as they update this off my website.

Any Ideas???  cheers



Rob.
0
Comment
Question by:RobJanine
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 16928571
Do other people use this Backend? Won't you have the problem of affecting other user's if u modify the table and not change other databases?


But you could try this


Public Sub AddNewField()

    Dim db As dao.Database
    Dim tbl As dao.TableDef
    Dim col As Object
   
    Set db = dao.openDatabase("C:\EE\EE2.MDB")
   
    Set tbl = db.TableDefs("tblOoops")
   
    Set col = tbl.CreateField("MyNewField", DB_TEXT)
    tbl.Fields.Append col
   
    db.Close
    Set db = Nothing
   
End Sub


Now this will add a new field called MyNewField in the table tblOoops
When that is done u need to refresh the tablelink

the other issue is, will it let u refresh on a MDE?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16928576
Now I tried using RefreshLink but that does not work well

    'Refresh link
    CurrentDb.TableDefs("tblOoops").RefreshLink


'So the alternative is to drop the linked table and recreate it

    DoCmd.DeleteObject acTable, "tblOoops"
   
    DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\EE\EE2.MDB", acTable, "tblOoops", "tblOoops", False


Again, this works fine for MDB, unsure for MDE
0
 

Author Comment

by:RobJanine
ID: 16928606
Thanks Rockiroads

Will try your code on mde database soon

How do I run this code. Only want it to run once, maybe on first start-up after download.

Cheers

    Rob.
 
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16928640
u need to decide when to run it
to run it, its a public procedure so just call it

AddNewField

Obviously the code I have supplied as no error checking so u may be best change it to a function, then return true/false if it worked or not.

0
 
LVL 26

Expert Comment

by:dannywareham
ID: 16928993
Hey Rocki - long time no see :-)

I use an update facility on one of my systems, that works well.

I have a shared network area that houses the back end, as well as a "spare" version of the front end.
Each user has their own front end.

On the database open, I run a query to pull data from the "spare" database. Basically, it looks at a table and if there's a change, I simply change a value in that table in the spare system.

If this query returns a true response to the cjange, the users db drops it's objects and imports all new ones from the spare database.

By doing it this way, I don't have to manually update front ends or send new front ends to all users, and it will only have an update when the front end is actually used by the user.

The downside is that you could have multiple versions between users (until they open them, of course). However, I find that the pros far outweigh the cons.

Just an idea  :-)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16929594
Hello RobJanine

I have developed a commercial Access application, shipped as MDE, together with a "template" BE database for the users to create new ones.

Each version can recognize and update all previous versions, some version changes meant some complex changes: new tables, changed relationships, changed data model, etc. Others were just what you have: add a single new field.

The most important thing would be to implement a version number to your BE databases. The FE would open the BE, check the version number, and then:
* if ok, proceed
* if too high, error message "can't do anything with a version 8.0, I'm only 6.0!"
* if too low, suggest to make a backup and upgrade the data file.

For example, FE expects a v.6.0 and is fed a v.4.3 BE. Your upgrade woul call in turn Upgrade_43_50(), Upgrade_50_51(), Upgrade_51_52(), and Upgrade_51_60()...

I used custom properties for that: <database object>.Properties. I found this better than having a table "tblVersionInfo" or the like for various reasons.

Users are quite familiar with the process. If you model you "upgrade" dialogs after those found in all major software, they will immediately understand what is going on and might not need any additional support from your part.

Needless to say, this is one area of your application where you do not want bugs...

(°v°)
0
 

Author Comment

by:RobJanine
ID: 16931627
Hi,
I like the sound of these..
Mine is also a commercially used Access application. I upgrade the front end about once a month and it is not that common to have to update the back end, but when I have to add a field or something it becomes a pain as they have to email me there backend then I update it and send it back, but they cant work on it while I have it.

Do you have any example code on how I would do this??

Thanks for your help

Cheers

    Rob.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 65

Expert Comment

by:rockiroads
ID: 16931907
Hi Danny! how ya doing

RobJanine, did that code of mine worked? I wasn't so sure if it would work on MDE or not. I assume it would since its just vba code your running
0
 

Author Comment

by:RobJanine
ID: 16931956
Yes it worked fine thanks
Just working on it now so it runs once only.
Made a Feild 'Verson Number' in the switchboard tble and hope to run code if 'verson number' = ......

Rob.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16931979
ok, thanks
Good to know.
0
 

Author Comment

by:RobJanine
ID: 16932133
Hi Again Rockiroads

Here is the code so far:

If (DLookup("[VersionNumber]", "BackEndVersion") = 1#) Then

    '***********Need code her to change 'VersonNumber' to 1.1 *************
       
    Dim db As dao.Database
    Dim tbl As dao.TableDef
    Dim col As Object
   
    Set db = dao.OpenDatabase("C:\Database Files\DBS QTCv2_be.MDB")
    Set tbl = db.TableDefs("TblBuildingSizeDetails1")
    Set col = tbl.CreateField("Test", DB_TEXT)
    tbl.Fields.Append col
   
    db.Close
    Set db = Nothing
   
    DoCmd.DeleteObject acTable, "TblBuildingSizeDetails1"
    DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\Database Files\DBS QTCv2_be.MDB", acTable, "TblBuildingSizeDetails1", "TblBuildingSizeDetails1", False
     
    End If

Can you help with some code to insert 1.1 into table 'BackEndVersion'

Cheers

   Rob.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16932181
If u want to increment using 1.0,1.1,1.2,1.3
why dont u store the field as a number then do this

ssql = "update BackEndVersion set versionnumber = versionnumber + 0.1"
docmd.runsql sSql

0
 

Author Comment

by:RobJanine
ID: 16932492
Thanks so much Rockiroads, works great.

Have just posted another question you might want to help with.

"Setting field properties using VBA code"

Cheers again


    Rob.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16934244
RobJanine

In some more detail.

1) Get the database object of the BE.

Const cMainTable = "tblReceipies"

Function BEDb() As DAO.Database

    Static sdb as DAO.Database
    Dim strPath as String

    If sdb Is Nothing Then
        strPath = Mid(CurrentDb.TableDefs(cMainTable).Connect, 11)
        Set sdb = DBEngine(0).OpenDatabase(strPath)
    End If
    Set BEDb = sdb

End Function

2) Use DAO properties to control version numbering

You can then use BEDb() whenever you need to do something with the BE. For example, use GetProperty and SetProperty to manage the version number. Look up "CreateProperty" in DAO help for the definition of these functions.

In your last code sample, you would use also BEDb instead of opening the database from a fixed path, as you did. Your customers might have placed the database elsewhere.

3) Make sure you anticipate all possible cases:

Version number too low, version number too high, version number not found (wrong database format), database corrupted, etc.

I'll look into your other question as well.

(°v°)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now