Solved

Updating a linked table from front end

Posted on 2006-06-17
14
409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Database 5 45
Database maintenance 36 101
Parameter Query 33 50
MS Access Combobox to Disable Controls on a Separate Form 10 24
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

756 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