Setting Database default paths

[Gaztech]

Hi,

I'm confused about this...

I have a fully running Access setup using several tables. Everything works just fine.

However, I want to be able to have a folder containing a copy of the development system which is the same (including any changes I make of course) as the live system.
My users are testing the live system user interface and testing/checking for errors, then making suggestions for improvements.

I wish to make a change in Development system, test it in that environment and then simply copy the whole thing to the live location. Note that this is only for development whilst I get everything right! I'm aware that when the system goes fully live I won't be able to do that as there will be changes in the live database taking place.

I have an input form in each location (development and Live). This form has a SAVE button with code as it takes info from the unbound fields on the form and then populates by data table with a new record. The issue I have is that the save button references an absolute path to the data table like this:

I have a variable in the Save button code that opens the database - but with an absolute path.

mydb = C:\Users\Devel\Desktop\AccessDevel\StkDatabase.accdb

The variable is used later on in the save button script to populate the table.

What I want to do is remove the absolute path and use a path that is not absolute in this statement so, if I'm using the Live system I want the path to point to that - or, if I'm using the Development system, I want the path to point to that location. I want the ability to be able to change something in the Development system and then copy the entire .accdb file so that it immediately works in the new location. Presently, in order to do this, I have to edit the absolute path every time I copy it across. It's annoying.

Is there a way to do this so that the code picks up the path for the Input Form currently running and uses that? I don't want to use a bound form as doing things the way I'm doing it is very flexible and there is a lot of checking going on when a record is saved.

I hope I've explained myself correctly... !

Any ideas guys - or am I asking too much...?

 

[Gaztech]

Or is there a way to read an .ini file to set this?

 

[gemma-the-husky]

Is the database split? Data tables in one database, everything else in another?

 

[Gaztech]

Is the database split? Data tables in one database, everything else in another?

No, I just want to copy the accdb file containing all of it over. This contains all the tables and the forms. I just want to copy the accdb file and run it directly from the new location.

 

[gemma-the-husky]

Well, sorry, but you don't want to do that. That will get you in knots.

What you ought to do is split the database so you have a front end with the code forms queries etc, and a back end with just the data tables. Then you link the front end to the data tables. The linked tables show in access with black arrows to indicate they are linked. Most things work exactly the same. There are one or two minor differences, which you may never actually experience.

You also should make sure that all your users are not running/sharing the same database. You must be doing this at present. It might work, but it is likely to go wrong at some point. So although multiple users CAN run the same database, we always advise against it. For example, if you store temporary data in a table, then if multiple users are all using the same database, users can overwrite each others temporary data. If you have separate databases this doesn't arise.

Now, to have a development copy you just take a copy of the real data, and link your front end to the copy. Now this is a development copy, you can develop new ideas against the development database, and release the new updated front end whenever you like - which is another reason why all users have separate front ends. The users can use version 1.1 while you are working on version 1.2. Keep safe unused copies of all your versions, and don't use them. They are just safe backups for when you get problems.

 

[Gaztech]

Ok. that seems to make a lot of sense.

I'm very new to Access and I didn't know you could completely separate things like this.

The terminology is a bit weird... Database... This is the whole thing including the tables - or can the tables be something completely separate?

To me, the database is the data structure - a set of linked tables. However Access seems to lump the forms into the same adddb (database + linked forms/queries etc.) file.

I guess now that you've said all this, I need to have a way to separate all the forms from the table structure. How is that "separation" done? I'm assuming that the "forms" structure is what you give to the user and the tables sit somewhere else. Is that what you're saying here?

I apologise for being an idiot about all this but it's not very intuitive that this is possible from what you are presented with at the main interface. The programming side isn't too much of a problem for me but the way everything links up seems to be at the root of my understanding here.

A rough explanation will sort it I think. I just need to know how to set up a "forms only" instance I guess and make it "see" the tables?

Or am I barking up the wrong tree? !!!

 

[MajP]

1. Create a blank database and call this something like MyDatabase_Backend.
3. Use the import feature to import nothing but the tables.
4. Compact and repair this database
5. Create another blank database and call this something like MyDatabase_FrontEnd
6. Link the tables from the backend.
7. Import all the remaining objects into this: queries, forms, reports, macros, module.
8. Update any references in the front end
Now you have a split database

Once you get that I can give you code that allows you to change backends using a file browser so you can switch from real to live whenever you want.

 

[Gaztech]

Ah.. just read up about splitting databases. Now things are clearer. The reasons all make sense too.

I'm not going to get around to getting any of this sorted for a couple of days now but I have a great starting point!

Many thanks to the contributors for your help! It's appreciated.

 

[MajP]

I'm not going to get around to getting any of this sorted for a couple of days now but I have a great starting point!

It is really very fast and easy. You cannot mess it up because you are making something new not changing what you have.

 

[gemma-the-husky]

Take a copy before you start. You can't have too many backups.

 

[Gaztech]

I decided to just get on with it... Split and Linked the databases. Works fine. Much better!

Now how can I provide a simple way to switch between backends for development purposes?

 

[MajP]

I have code I can send you, but do not have it here. If no one gets back sooner, I will send this evening my time (US East Coast).

 

[gemma-the-husky]

You can connect the tables manually - I am sure you can do that temporarily as the users won't need to do it.
You don't really want users having to work out how to do that - it's much better to do it in code - so get the code from @MajP and you should be able to get it working.

 

[Gaztech]

@MajP - Thank you! I really appreciate this!

Everything is working nicely

 

[MajP]

I cleaned up your code a little

Public Function DeleteRecord()
On Error GoTo errlbl:
  Dim Msg, Style, Title, Response, MyString As String
  Msg = "Are you sure you want to delete/undo this item?"
  Style = vbYesNo + vbQuestion + vbDefaultButton1
  Title = "Confirm Delete" ' Define title.
  Response = MsgBox(Msg, Style, Title)
  If Response = vbYes Then ' User chose Yes.
   If Me.NewRecord Then
     Me.Undo
    Else
     DoCmd.RunCommand acCmdDeleteRecord
     DoCmd.GoToControl Screen.PreviousControl.Name
    End If
  End If
 Me.Refresh
 Exit Function
errlbl:
   DoCmd.Beep
   MsgBox Err.Number & " " & Err.Description & " In Delete Record"
End Function

You had a lot of repetitive statements.

Now you select both the form and all the controls. If you put this in the double click
=DeleteRecord()

then it will work wherever you click except blank areas. Not sure if you are using a datasheet or continuous form.

 

[MajP]

Here is the code to swap backends.
1. There is a frmLink that opens hidden. This then checks for a backend and if not asks you to browse to it. I provided 2 backends
2. mdlRelink has the majority of the code
3. mdlFileDialog has the file dialog and you need a reference to MS Office

I did not write this code and since it works I have not spent time trying to understand how it all works, but I have used it for years and have most of my dbs setup this way.

 

[The_Doc_Man]

Actually, it is pretty easy to figure out where you are and to act on it.

If you test CurrentDB.Name, you get the fully qualified device/path/name.type of the front-end file. If you examine one of the linked tables, the .Connect string is the fully qualified device/path/name.type of the back-end file. Among other things you could put some smarts in the file for it to "know" that is the DEV copy and to look for the back end files in a sub-folder of your DEV directory.

I was with the U.S Navy for 28+ years. What we did was a four-directory situation. Some folks can't afford or don't want to bother with that much, but I'll tell you what we did. Our server had four directory trees. The /DEV/ folder held the current, unsecured development copy. Folder /DEV/BE/ held the development copy of the back end. It FOUND the back-end by finding the front-end name and dinking around with the dev/path/ portion. When it was time to test, we dropped things into the /TST/ folder and used the /TST/BE/ folder for the testing back-end. It truly didn't matter what happened to the /TST/ folder because it would get erased fairly often.

BUT... eventually we had a copy that was ready for promotion. We copied that to /STG/ (the staging directory). Unlike the other two, the staging copy NEVER ran locally and never had its own back-end file. Instead, that was the one where we started securing the database front-end by setting up the opening files, enabling the code to turn off the ribbon and the bypass functions. That one, we linked to the /PROD/BE/ folder just before we moved the (now staged) copy to /PROD/ for general use.

Unlike the /DEV/ and /TST/ and /STG/ folders which only allowed file permissions to the maintenance team, /PROD/ allowed MODIFY access to the users of the Domain group we created for the DB's users - because the /PROD/ folder was owned by the group and all permissions were centered around the group.

The front-end was always sitting there in the /PROD/ folder and the back-end was always there in /PROD/BE/ - but if you tried to run the front-end directly from there, it saw the FE's path contained /PROD/ and balked. The back-end had all of the tables but it DID have one other thing - an opening form that ALSO disabled the ribbon and bypass keys and would balk you if you tried to open the back-end directly from Access rather than from the front-end.

We wanted to, but could not, use an auto-download batch file on each user's machine. Some obscure Navy regulation blocked that. So instead we had an annunciation block on the main startup form that would advise users if they needed to close out their local copy and pick up a new copy from the master folder.

Security wasn't the tightest, I'll admit, but it was a Navy domain and if you tried to break through and ended up damaging the files, we would have a log from the network manager's firewalls and routers. Folks knew that if they intentionally screwed with the DB in /PROD/ that they would get screwed in return, and the Navy's idea of "getting screwed" was pretty explicit. Though the project's Master Chief Petty Officer DID advise me that keelhauling was no longer allowed.

The point of all of this? You claim that you are new to Access development. What the Navy had us do was compliant will regulations regarding the care with which we put live code out to our users. You might not need that much - but it wasn't that hard to do.

 

[Gaztech]

Here is the code to swap backends.
1. There is a frmLink that opens hidden. This then checks for a backend and if not asks you to browse to it. I provided 2 backends
2. mdlRelink has the majority of the code
3. mdlFileDialog has the file dialog and you need a reference to MS Office

I did not write this code and since it works I have not spent time trying to understand how it all works, but I have used it for years and have most of my dbs setup this way.

Hi @MajP ,

Well.. over the weekend I installed the backend swap code. It was a bit of a learning curve to get it installed to my setup but it's now in there - and it works just fine.

This is going to make my life a lot easier!

Since it was a bit of a pain to install it, I'm going to write up a guide so that if I need to do it in future, it will be easier but many thanks for this. I really do appreciate all the contributions from everyone here. I suppose we were all newbies once...

No doubt, I'll be back with more queries but for now all is well.

 

[Gaztech]

Hi guys,

This has worked out much better than I thought. I was using some absolute paths for the Save buttons on my forms which have many unbound elements. This caused an issue when changing backends but I found that the relative path variable for the "changed" database is available public so I'm able to read this value and use it for my save buttons code. This makes everything very flexible.

I'll be using this code for some other stuff I'm writing so I have to say a BIG THANKS to MajP for this code. It's solved a whole load of headaches for me!

 

[MajP]

Since it was a bit of a pain to install it, I'm going to write up a guide so that if I need to do it in future, it will be easier but many thanks for this.

Sorry, I was hoping you got back after looking at the demo for help. I think it is very simple to install, you just need to know the few steps. I am guessing you did not know about the hidden form at start up. I use a form, but you could also use an autoexec macro to verify the links at startup. that is probably a cleaner solution since you do not have to make the hidden form the default. It removes a step. If I get time I will do that.