Frontend Backend Database Questions

[robrich22]

Go back up there and check my post again. I had to repost my code.

[data808]

What exactly does this mean with framework 4.0? Will it work on all versions of Windows? Or MS Offices?

Yes, I would be interested in checking out the final version. Is it pretty user friendly? Do you have a tutorial or video of how it works? I would love to contribute to the code if I could but I don't know any languages at the moment. I hope to change that someday.

[Xipooo]

Also to note, I will be releasing a free utility for frontend sync'ing, launching, and relinking in about 2 weeks. It is a C# .net application that will run on .net framework 4.0. You can view or contribute to the project at https://github.com/robrich22/DBConnect

It's actually usable in it's current form, I am just adding some finishing touches to it. Send me a msg if you want a link to the install/final version.

Thanks

I've solved it without a 3rd party app thank you.

[robrich22]

What exactly does this mean with framework 4.0? Will it work on all versions of Windows? Or MS Offices?

Yes, I would be interested in checking out the final version. Is it pretty user friendly? Do you have a tutorial or video of how it works? I would love to contribute to the code if I could but I don't know any languages at the moment. I hope to change that someday.

Yes it will run on all versions of Windows. User Friendly? Yeah it should be <grin> . It's fairly straight forward. You will direct the app to the FrontEnd Location, BackEnd location, and Master FrontEnd (network drive, or ftp location). It will fetch the new master frontend and update the local frontend to reflect the new changes.

It will also have options for Compact Repair, Table Relink (So when you move your FrontEnd & BackEnd, it will re-sync them before you try to load the frontend).

It's mostly to just make development easier... You can of course do all this functionality yourself, or make scripts instead of a .net app But it's a learning project and will make my life alot easier maintaining my Access Database project.

[data808]

Wow this looks crazy. I see the first section is a private function. So where does this part go? In a module? Or in the immediate window for the cmdLogon_Click event? Sorry for the stupid questions.

Then I also see that the last part is for a log for when the user logs into a form. If I understand correctly, as of right now this part would not apply since I do not have a table that keeps that sort of recording. Is that correct? This would be a feature I would want eventually but right now I need to just focus on getting this login form to work.

Would you be able to highlight the areas of your code to tell what needs to be adjusted to mine? Like text box names and what not. I know some areas of your code but I'm pretty sure I'm going to miss some things and then it won't work. If its too much trouble then its ok. Thanks for all the help anyway.

[data808]

Yeah this sounds exactly what I need to upkeep and maintain this database. Right now, I realize that when I make changes to my master frontend file, I will have to physically go to each computer and replace the user's frontend file with the new one. That would be a pain in the butt.

What exactly is the compact repair? I believe I ran into this before and if my memory is correct, it happened on a 2010 version of office. My database was built from 2007 but i occasionally go to 2010 and 2013 when my laptop is not available. But from what I remember I think the compact repair might have messed up my database. Like it got corrupted or something where it would no longer work on 2007. It might have been a new feature that was not available in 2007 and no longer recognized the file.

How does the Table Relink work? If I move the backend table, how will your program know where to find it? Do I still have to set up the path manually? Or will it just scan the whole drive for that one backend file and relink that way?

When you say make scripts, would that count as a batch file? I found a batch file on a forum that was supposed to do what your app is claiming to do. I tried but not on a network environment. Just in different locations on the C Drive and I couldn't get it to work. Again I probably just didn't customize the batch file code completely which is what I am afraid will happen to this VBA you are sending me. Haha, yeah, stupid I know. Still learning. I must say that this is extremely interesting though and I can't stop trying to learn more about databases.

[robrich22]

Wow this looks crazy. I see the first section is a private function. So where does this part go? In a module? Or in the immediate window for the cmdLogon_Click event? Sorry for the stupid questions.

No problem. There are no stupid questions. "Private" is a scope declaration. It means that the function can't be used outside of the current module. You can call that function from inside the module it is in. I suggest you check out one of the many Visual Basic for Applications tutorial websites. They will give you a primer on functions. You would cut and paste the function code inside of the module that you intend to use it in. The module is the code-behind file for the Form.

Then I also see that the last part is for a log for when the user logs into a form. If I understand correctly, as of right now this part would not apply since I do not have a table that keeps that sort of recording. Is that correct? This would be a feature I would want eventually but right now I need to just focus on getting this login form to work.

Would you be able to highlight the areas of your code to tell what needs to be adjusted to mine? Like text box names and what not. I know some areas of your code but I'm pretty sure I'm going to miss some things and then it won't work. If its too much trouble then its ok. Thanks for all the help anyway.

That was a bit of code from one of my databases. It logs whenever a user attempts to login to the form. Yes you would need another table to utilize that function. I can give you specific directions if you want to implement it.

You have about 3 different ways that were posted for the Login form. Once you get that finished, let me know and I'll get you started with logging application logins.

[robrich22]

Yeah this sounds exactly what I need to upkeep and maintain this database. Right now, I realize that when I make changes to my master frontend file, I will have to physically go to each computer and replace the user's frontend file with the new one. That would be a pain in the butt.

Yeah I make changes pretty frequently to my FrontEnd and it's a pain to go around and update each workstation.

What exactly is the compact repair? I believe I ran into this before and if my memory is correct, it happened on a 2010 version of office. My database was built from 2007 but i occasionally go to 2010 and 2013 when my laptop is not available. But from what I remember I think the compact repair might have messed up my database. Like it got corrupted or something where it would no longer work on 2007. It might have been a new feature that was not available in 2007 and no longer recognized the file.

The easy answer is, compact and repair: It will make the database file smaller, and the database will run quicker. For a technical explanation check out this link http://office.microsoft.com/en-us/access-help/compact-and-repair-a-database-HA010341740.aspx

How does the Table Relink work? If I move the backend table, how will your program know where to find it? Do I still have to set up the path manually? Or will it just scan the whole drive for that one backend file and relink that way?

The current version of my table relinker, requires the user to manually select the FrontEnd, BackEnd, and Master FrontEnd locations. I'll eventually put in a Smart BackEnd locator. Not sure how it will work yet.

When you say make scripts, would that count as a batch file? I found a batch file on a forum that was supposed to do what your app is claiming to do. I tried but not on a network environment. Just in different locations on the C Drive and I couldn't get it to work. Again I probably just didn't customize the batch file code completely which is what I am afraid will happen to this VBA you are sending me. Haha, yeah, stupid I know. Still learning. I must say that this is extremely interesting though and I can't stop trying to learn more about databases.

Yeah I soppose a batch file could be a script. It's similiar. A batch file normally just runs shell commands, a script such as vbscript can make system calls I believe.. Someone else can chime in on that.

[data808]

Anyone know where to get a good free frontend updated?

[June7]

What does that mean?

[data808]

Sorry. Frontend updater. I need something to update my frontend files when I make changes.

[Xipooo]

Have a look at this thread:

https://www.accessforums.net/code-re...end-33331.html

[data808]

Thanks I'll try the batch file.

[data808]

Ok so I tried that batch file but was not able to get it to work. I tried changing all the sections of the code to fit my environment but whats weird is that when Access was trying to open it said that it could not understand one of the command lines in the batch file and that I have to make sure the path is correct. Also had another prompt afterward saying it was looking for an .mdb file that it could not find. The weird thing is that I don't work with .mdb's. My database is an .accdb file which I did add to the batch file code. Can anyone explain exactly what I need to change to make it work? I followed the instructions. I should also mention that I changed all of the parts that say %userprofile%blah blah blah...to where my local file was being held. Was I not supposed to? I also don't know how to deactivate the runtime part of it so I just deleted the runtime word from the code. Here is the code:

@echo off
REM file used to automatically update the Database when updates are available.
if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.accdr echo Database needs to update to newer version.
if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.ac cdr pause
@echo off
if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.ac cdr if exist %userprofile%\appdata\local\redirect\ui*.accdr del %userprofile%\appdata\local\redirect\ui*.accdr


if not exist %userprofile%\appdata\local\redirect\ui1.3.2013.ac cdr Robocopy \\DUI_NAS\data\database %userprofile%\appdata\local\redirect ui1.3.2013.accdr


Start MSAccess.exe /runtime %userprofile%\appdata\local\redirect\ui1.3.2013.ac cdr


Exit

Where local file is kept
File name
Message that pops up in a CMD window to let the user know the file is updating. They will be prompted to press any key.
This line checks for previous versions and deletes them prior to copying the new version
Location of the Master Copy on network drive.
Runtime switch is only needed if you run your database in runtime mode or your users don't have the full version of Access

Here is what I changed it to:

@echo off
REM file used to automatically update the Database when updates are available.
if not exist C:\Windows\Clearance Local File\Master Clearance Form Updated 03-21-2014.accdb echo Database needs to update to newer version.
if not exist C:\Windows\Clearance Local File\Master Clearance Form Updated 03-21-2014.accdb pause
@echo off
if not exist C:\Windows\Clearance Local File\Master Clearance Form Updated 03-21-2014.accdb if exist C:\Windows\Clearance Local File\Master Clearance Form Updated*.accdb del C:\Windows\Clearance Local File\Master Clearance Form Updated*.accdb


if not exist C:\Windows\Clearance Local File\Master Clearance Form Updated 03-21-2014.accdb Robocopy G:\Desktop Setup\Clearance Master File\ C:\Windows\Clearance Local File Master Clearance Form Updated 03-21-2014.accdb


Start MSAccess.exe / C:\Windows\Clearance Local File\Master Clearance Form Updated 03-21-2014.accdb


Exit

I realize that the files are the same in the local location and the network location but I wanted to just see if the code would still open the file which it did not. Because from what I understand is that if the file the same then it won't download from the network drive to the local drive, only if its a different file will it do that. Is that not correct?

[data808]

Has anyone used the above batch file successfully?