By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Recently Split Database

100+
P: 166
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!
Feb 9 '09 #1
Share this Question
Share on Google+
18 Replies


Expert 100+
P: 1,287
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!
Feb 9 '09 #2

100+
P: 166
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?
Feb 9 '09 #3

Expert 100+
P: 1,287
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.
Feb 9 '09 #4

100+
P: 166
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?
Feb 9 '09 #5

Expert 100+
P: 1,287
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.
Feb 9 '09 #6

100+
P: 365
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
Feb 10 '09 #7

NeoPa
Expert Mod 15k+
P: 31,419
@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.
Feb 16 '09 #8

100+
P: 166
@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
Feb 17 '09 #9

NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 17 '09 #10

100+
P: 166
I was just trying to get some clarification on my original question...I'll post the other half in a new thread.
Feb 17 '09 #11

NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 17 '09 #12

100+
P: 166
@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
Feb 17 '09 #13

100+
P: 365
Hi, the following code which (for me) runs on startup

Expand|Select|Wrap|Line Numbers
  1. Function VersionCheck() As Boolean
  2. Dim VN As Integer, meVN As Integer, fn As String, MDEfn As String
  3.     VN = DMax("Version", "tblVersion")
  4.         'DoCmd.RunSQL "UPDATE tblVersion SET tblVersion.Version = " & VN + 1 & ";"
  5.     MDEfn = """" & DLookup("MDEfn", "tblVersion", "Version = " & VN) & """"
  6.     meVN = 2
  7.     If VN <> meVN Then
  8.     VersionCheck = True
  9.     MsgBox "The version you are currently using has been superseded by another;" & vbCrLf & _
  10.             "The program will close and retrieve the latest version before continuing.", vbCritical, "Program Update Available..."
  11.         fn = FolderFromPath(CurrentDb.Name) & "Update.cmd"
  12.         Open fn For Output As #1
  13.         Print #1, ":CheckLoop1"
  14.         Print #1, "Copy " & MDEfn & " " & """" & CurrentDb.Name & """" & " / y "
  15.         Print #1, "If ErrorLevel 1 GoTo CheckLoop1"
  16.         Print #1, """" & CurrentDb.Name & """"
  17.         Print #1, "Del " & """" & fn & """" & " /f"
  18.         Close 1#
  19.     Call Shell(fn, 1)
  20.     DoCmd.Quit acQuitSaveAll
  21.     End If
  22. End Function
Expand|Select|Wrap|Line Numbers
  1. Public Function FolderFromPath(strFullPath As String) As String
  2.     FolderFromPath = Left(strFullPath, InStrRev(strFullPath, "\"))
  3. 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,
Feb 17 '09 #14

NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 17 '09 #15

100+
P: 365
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

Expand|Select|Wrap|Line Numbers
  1. Attrib -H "FileName" 
  2. *Code*
  3. Attrib +H "Filename"
Feb 17 '09 #16

NeoPa
Expert Mod 15k+
P: 31,419
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.
Feb 17 '09 #17

100+
P: 166
@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
Feb 18 '09 #18

100+
P: 365
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
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 18 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.