Recently Split Database

[csolomon]

Hello:

I recently split my database. I emailed the FE to users. I wanted to know, if and when I need to make a change to it or add another piece to it, how would I go about updating the DB, through my local FE or the BE, which only holds the tables? I have a app that i've been working on and when I finish, I plan to merge it to my DB...how can I accomplish it now that the DB is split?

Thanks!

[ChipR]

The usual way will be to create a new version of the FE, email it, and tell users to replace their old FE with this new one. Make sure you use a backup copy of the backend for any work you do on the frontend!

[csolomon]

OK, so I need to make a back up of my back end.
I make changes on the front end. If I need to add tables, I add them to the BE and add the forms and queries to the FE, then re-link the tables in the FE?

[ChipR]

RIght. Then you can just copy the new tables from the backup BE to the actual BE at the same time that you start using the new FE.

[csolomon]

when you say 'back up backend' what do you mean? I don't have a back up backend...what I did was make a copy of the original DB and import tables from that to a new DB. I then deleted all of the tables from the original DB and made the the FE...and distributed that. So inessence, I would be making the changes to the original DB and re-making the FE for distribution?

[ChipR]

Don't do any work on your FE while it's connected to your live data in the BE. It's not worth the risk. Put a copy BE somewhere and relink the tables in your FE to that. When you are ready to release, make a backup of the live BE, then update the live BE and relink the FE to it and distribute.

[Dan2kx]

I created some code to check a hard coded variable against a table value, followed by a batch file that writes itself and pulls the new version down, if you wish i will post.

Dan

[NeoPa]

@csolomon
Back up - make a copy of.
Backend - BE database.

Essentially, the suggestion is to make a copy of the BE database and develop your changes in the FE database such that it uses the backup copy of the BE rather than the live version.

I think that's what's meant. Correct me if I misconstrue.

[csolomon]

@NeoPa
OK. So I have been working on the BE copy to do some developing. I was wondering when it's time for me to transfer those changes to the main FE, how would I go about doing that? Importing the forms from my local FE to the main FE?

Also, each of my users use certain forms. I was wondering if I could customize each FE based on what the user should see.

Thanks

[NeoPa]

I suggest we stick with the original question, at least until it is clear that it's resolved. This thread is already getting confusing with questions going off in various directions.

I think that a question on how to design your database(s) in an FE/BE setup with your specified restrictions is not something for this thread at all.

[csolomon]

I was just trying to get some clarification on my original question...I'll post the other half in a new thread.

[NeoPa]

I'm not trying to be difficult. A confusing thread is one that's less likely to get further help is all.

In your position I'd consider replying to Dan's last post about some stuff he's already developed. I suspect that may be quite useful to you.

[csolomon]

@Dan2kx
Hi Dan,

If I understand what you said, you have a program that updates all of the FE's without my having to resend them when I update? I think this may be useful to me. I would appreciate if you posted.

Thanks,

csolomon

[Dan2kx]

Hi, the following code which (for me) runs on startup

Function VersionCheck() As Boolean
Dim VN As Integer, meVN As Integer, fn As String, MDEfn As String
    VN = DMax("Version", "tblVersion")
        'DoCmd.RunSQL "UPDATE tblVersion SET tblVersion.Version = " & VN + 1 & ";"
    MDEfn = """" & DLookup("MDEfn", "tblVersion", "Version = " & VN) & """"
    meVN = 2
    If VN <> meVN Then
    VersionCheck = True
    MsgBox "The version you are currently using has been superseded by another;" & vbCrLf & _
            "The program will close and retrieve the latest version before continuing.", vbCritical, "Program Update Available..."
        fn = FolderFromPath(CurrentDb.Name) & "Update.cmd"
        Open fn For Output As #1
        Print #1, ":CheckLoop1"
        Print #1, "Copy " & MDEfn & " " & """" & CurrentDb.Name & """" & " / y "
        Print #1, "If ErrorLevel 1 GoTo CheckLoop1"
        Print #1, """" & CurrentDb.Name & """"
        Print #1, "Del " & """" & fn & """" & " /f"
        Close 1#
    Call Shell(fn, 1)
    DoCmd.Quit acQuitSaveAll
    End If
End Function

Public Function FolderFromPath(strFullPath As String) As String
    FolderFromPath = Left(strFullPath, InStrRev(strFullPath, "\"))
End Function

I have a linked table "tblVersion", that stores a number which can be changed obv. and the path to the updated file, mde/mbd. this function holds a hard coded value (to be changed on new release), if the values dont match then the function writes a batchfile, closes access and the batch runs. it is very crude and has the potential for error (because of the loop, but you could put a counter in) but i like it.
i hope this is clear and can help you,

[NeoPa]

That's kinda neat Dan :)

I have a similar one in a spreadsheet that saves only one of the sheets to a new file and FTPs the file to a server. Never done it in Access before though.

[Dan2kx]

Thanks NeoPa, i must credit you on the Checkloop syntax which you gave me in a previous post; (http://bytes.com/topic/access/answer...e-reopen-db-vb), i also add another line on mine to resolve hidden files

Attrib -H "FileName" 
*Code*
Attrib +H "Filename"

[NeoPa]

I see. Yes that probably helped a bit.

What I was referring to though, was the way you created the CMD file in your Access VBA code. I don't recall helping with that (although I've contributed to a few threads on a variety of topics in my time so it's hard to be positive). That's certainly good to see and I was impressed.

[csolomon]

@Dan2kx
--Hi Dan, Thanks for the post.

I noticed you had "and the path to the updated file, mde/mbd", does that mean this will not work with Access 2007, which has the file type .accde? Also this code is put in when the DB starts? Where would that code go? In a module or and update event somewhere.

Thank you

[Dan2kx]

sorry i meant put the path to file (with the extension),

you could hard write that too if you require, i choose to store that in the version table and use a dlookup.

WRT location of code, you can put it where ever you want, what i posted was a function, so this can go in a module, and you simply need to refer to

VersionCheck

in some code that runs on startup, alternatively you can just use the code on its own (remove top and bottom Function lines).

i use an auto exec macro (in a2003) that runs on startup some other "Startup()" code and the VersionCheck resides there, you could have a form load on startup and have this code in the On Load/Open etc (of the form)

Good luck, let me know if you need any further advice

Dan