MS Access General Information

May 16th, 2013

MS Access – Database Created with the 32-bit version …

Have you ever developed a database using a 32-bit Access version and then tried to deploy it on a 64-bit version and receive the error: “This database created with the 32-bit version of Microsoft Access. Please open it with the 32-bit version of Microsoft Access”.

First let me state that it is not recommended to use the 64-bit of MS Access. So right from the start, this is a bad idea!

Regardless, what is the solution. Actually, it isn’t a very hard fix.

Make a copy of the 32-bit database and recompile on a 64-bit version of MS Access. So basically at the end of the day, you need to start maintaining 2 versions of your db: a 32-bit version and a 64-bit version. Once you copy over the 32-bit version to the 64-bit version, Decompile the database, Compact and Repair, Compile (address any issue that may arise such as missing references, etc…) and finally Compact and Repair. Then you can distribute your database or convert it to an mde/accde format…

So yes, as a developer, if you are going to be distributing a database to users who have a 64-bit version of MS Access, then you need to also have a 64-bit version of MS Access.

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

March 19th, 2013

MS Access – Who is Logged In?

Another common need for most database administrators & developers is to be able to see who is logged into a database. A very common solution to this in the past has been to use the Jet UserRoster, see:

http://support.microsoft.com/kb/285822
http://support.microsoft.com/kb/q176670
http://www.utteraccess.com/forum/Who-s-Logged-Who-s-Con-t1897146.html
http://www.rogersaccesslibrary.com/misc/misc.htm (see: WhosOn97)

I personally find much more useful to create a table to log the database usage by the users and have a form open in hidden mode at the database startup to populate the table in question. There is also the added benefit that this method permit much more detailed information, and historical review (not possible with the UserRoster). The historical data can be very useful when determining users that did not disconnect properly (which can lead to corruption) from the database. The extra information can also be useful when troubleshooting problems.

So how can you create this simple log tracking form? Very easily and here is how:

First, we need to create a table to log the comings and goings within our database. Please note that you can add or remove the quantity (number of fields) you wish to keep track of. To do so you can use the following Data Definition Language (DDL) to create the corresponding MS Access table:

CREATE TABLE tbl_Db_UserTracking 
   (UTEntryID AUTOINCREMENT PRIMARY KEY,
   OSUserName VARCHAR(255) NOT NULL,
   ComputerName VARCHAR(255),
   ComputerIP VARCHAR(20),
   DbEntry datetime NOT NULL,
   DbExit datetime);

Then we need to create a login tracking form which will get open at startup in a hidden mode (so the user never actually sees it). Below is one example of this form. Since the form is never actually visible, you need not waste any time formatting it or performing any special layout… A bare minimum is required.
Then we need to setup a method to automatically launch the form at startup. Since all of my databases use an AutoExec macro to perform a number of steps (relink the table, check user permissions, stratup logoff forms, …) at starup it is only logical to merely add this to the function that the AutoExec macro calls.
Then we need to create a form for the administrator to use to review the data.

Share and Enjoy

Category MS Access, MS Access General Information | Comment No Comments »

Tags: Database Samples MS Access

January 20th, 2013

MS Access – Deploying an Access Database using CITRIX

This is a preliminary posting on the subject, one that I hope to add to over time.

We see many postings on various forums from people wanting to know more about running an Access database via CITRIX.

Firstly, since Access is not recommended to be used over a wireless connection, nor a WAN, CITRIX is a very interesting solution to giving access to such databases. That said, like everything in life, there are both pros and cons to such a deployment of your database.

Like with every MS Access database, to deploy it, you must first split the database into 2 components: front-end and back-end and then issue a copy of the front-end to each of your users. This can easily be done in CITRIX.

Typically, in an effort to minimize the impact of internal networks…, I like to try and have the back-end installed directly on the CITRIX server itself. By doing so, the database runs completely on the CITRIX server and there is no network impact, and the database also doesn’t take up valuable network bandwidth uselessly. Of course, as with any installation, a reliable backup system must be put in place and validated on a regular basis.

As a developer, I always need to be given an administrative console with the full version of Access installed so I can perform tests, and more importantly compile my application locally to ensure proper compatibility.

Where I have seen issue occur… Well, there can be a number of issues, but 2 specific issues seem to keep coming up recently.

Insufficient Server capacity – Since the CITRIX server is an application server, it is crucial to ensure sufficient RAM, CPU power,…. I recently had to identify the fact that the CITRIX server was maxing the CPU and thus the database application I designed was miserably slow. The IT department stated that the server was adequate so I added some custom code to log RAM and CPU usage to profile the server since they would not do so. I was able to clearly demonstrate that the CPU was saturated. Once they unloaded a few other applications from the server, all of a sudden my databases were running fine. The funny thing was that the database was throwing network error, nothing relating to CPU, or anything remotely leading one to believe it was a resource problem. Even more of a reason why the IT department was unwilling to believe my initial questions regarding their server. All of this to say, once you set up your db, ask the IT dept to profile the server CPU, RAM, Network just to ensure everything is optimal. it is so simple for them to do and can truly avoid weeks of headaches (for everyone). This is also something that could be checked from time to time.
Outdated/different server installations – Like any computer, the local setup can have an impact on application running on it. You can develop a superb database application and once deployed on the server it may not work. Why, simple because of a conflict caused by updates, difference in dll, exe, … This is why in my opinion (and some people argue this point) that the CITRIX server be kept up to date both its OS and Access/Office installation. Furthermore, it is crucial to compile your database on the local CITRIX server to ensure everything is proper.

That said, I must say that once things are setup properly, CITRIX can be very reliable and a very useful tool.

Share and Enjoy

Category MS Access General Information | Comment 2 Comments »

Tags: MS Access

December 4th, 2012

MS Access – Where to Start Learning – Database Tutorials

I always find it amazing that even with the internet, it can sometimes be very hard to sift through all the available material to find good tutorials and learning tools. Below are a few good starting points. Some are MS Access specific, but many are just database generalities whoses techniques can be used with any database. So if you are stating off, or merely brushing up, here are a few good links to consider:

For staters, their some of the best site are those of the various MS Access MVPs. I have compiled a seperate list of the website in the following post: http://www.devhut.net/category/mvp-websites/

Also, you will find a series a links below covering a variety of subject. I cannot take the credit for this listing, but thought it was so good that it should be posted for all to see:

As you can see, there are a number of articles from UtterAccess.com. This is one of the best, if not the best, one stop for Access related material. They have a large archive of articles, VBA functions, samples and they have one of the most active sets of forums. If there is one place to check out, http://www.utteraccess.com is that place!

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

September 29th, 2012

MS Access – Persistent Connection in a Split Database

Anyone that knows anything about developing a database knows that best practice dictates that you split your database into 2 components: a Back-End containing the tables (your data) and a Front-End containing everything else (queries, forms, reports, macros, modules, …).

What many developers seem to overlook however is the importance of always creating a persistent connection between the Front-End and the Back-end.

In a standard split database, each time you run a query, open a form, run a report, the Front-End must first establish a connection with the back-end, then when you close that object the connection is dropped/closed. Then you open another object, it must, yet again, establish a new connection, … You get the idea. Each time, establishing the connection takes time. How much time depends on numerous factors, your network, is the database secured/ encrypted, …

Think of it this way, imagine you have a multi-question survey to call people with. Is it better to dial a number, wait for someone to pick up at the other end, explain who you are, ask your first question and then hang up. Then repeat the same steps for your second question. And then yet again for your third question, … OR does it not make more sense to dial a number, wait for someone to pick up at the other end, explain who you are, ask your first question, ask your second question, ask your third question, …, and only hang up once your are through with your survey! The proper answer to this question is pretty clear cut if you ask me. Well, it is the same for your database!

To minimize this impact on your database and improve performance it is critical, IMHO, to always establish a persistent connection as soon as your front-end load. This way the database need not create a new connection each time you choose to do something, it already exists, so it can simply use it.

How to Create a Persistent Back-End Connection
Nothing could be simpler!

In the Back-End

Create a new table (you can name it anything you’d like)
Create a couple text or number fields of your choosing to the newly created table
Add 2 or 3 simple records to the newly created table

In the Front-End

Link the newly created table from your Back-end
Create a new form based on this table
Part of your AutoExec Macro’s code, add a line to automatically launch the form at the startup of your database in hidden mode.

So what have we done? Quite simply, since, as I stated previously, opening any bound object forces the Front-End to establish a connection with the Back-End, we are simply opening this form so it will establish a connection back to our table. We set it in hidden mode so the end-user isn’t ever even aware that it is there, and so they do not accidentally close it.

For complicated, multi-Back-End databases, a separate persistent connection should be established with each Back-End file.

Share and Enjoy

Category MS Access, MS Access General Information, MS Access Tables | Comment 3 Comments »

Tags: MS Access MS Access Tables

September 15th, 2012

Windows Update KB2596856 / MS12-060 Breaks Microsoft Windows Common Control Library (MSCOMCTL.OCX)

On Tuesday, August 14, 2012, MS released a new update which in fact crippled any application which used the Microsoft Windows Common Control Library (MSCOMCTL.OCX)! So anyone using any of the following Microsoft Windows Common Controls:

Animation control
Button
Combo box
ComboBoxEx control
Date and time picker
Edit control
Flat scroll bar
Header control
Hot key control
Image list
IP address control
List box
List-view control
Month calendar control
Pager control
Progress bar
Property sheet
Rebar control
Rich edit control
Scroll bar
Static control
Status bar
SysLink control
Tab control
Task dialog
Toolbar
Tooltip
Trackbar
Tree-view control
Up-down control

There have been numerous disccusions on the matter, amongst them:

And numerous proposed solutions, amongst them:

That said, the true solution, which partially worked for me (it resolved my computer’s problem and got me back up and running but a serious compatibility issue with all my clients still remains! See below for more information) was finally release by MS (after the community had already done all the leg worked an figured it out on our own) and was posted at: http://support.microsoft.com/kb/2597986.

All of this is very nice, but there remain 2 major issues:

This solution relies on the end-user doing all the fixing, on every computer! Why has MS not released a hot fix that would automatically have fixed this?! MS screwed-ed big time and sadly has passed the puck on to everyone else, rather than addressing the issue.
Furthermore, even if you fix your computer, your application will no longer work on any computer that hasn’t had this update applied!!! Just a little problem if you ask me! So in fact their solution does not truly resolve the problem entirely as their remains a major compatibility issue at the end of the day.

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

August 14th, 2012

MS Access, WANs, Wireless Networks and the Internet

A common question, and increasingly so, is relating to using MS Access over WANs (Wide Area Networks – which the Internet qualifies as).

The short answer: Keeping in mind the fact that MS Access uses the Jet/Ace database engine, MS Access simply does not, should not be used in any manner over WANs, wireless networks and/or the Internet.

The longer answer: As with most things in life, there are always ways to bend the rules… As such, there are techniques that permit one to use a database over WANs: the use of Terminal services, CITRIX, which the newer version of MS Access (2010 especially) developing your database as a Web database and publishing it to Office365.com, utilizing SharePoint services. Albert Kallal, an MS Access MVP, has a very detailed post about MS Access and WANs which covers much more in detail the subject, especially the technical reasons: http://www.kallal.ca/Wan/Wans.html.

That all said, even though there are certain work arounds (each having their own set of pros and cons), I always ask my clients the same basic question: ”What is the purpose of the database you are creating?”. If it is an internal business tools (used over a wired LAN network), then MS Access is definitely worth exploring further. If on the other hand, it is to be shared, over the Internet or corporate WAN, with lots of users/clients, etc. then it is time to look at web databases (which Access simply is not – at least not in the standard sense of the term). This is when you have to truly start looking into databases such as: MySQL (very common and cheap hosting is easy to find), PostgreSQL, SQL Server (harder to find host and more costly typically) and development languages such as: PHP, .Net, JAVA, and a multitude of others.

In summary:
In-house application used over a wired LAN connection-> MS Access is the apex in development tools! It is the fastest and probably easiest development tool used by millions, upon millions of users around the world!!!
An application to be run over a WAN, Wireless Network and/or the Internet -> Turn to more professional web application and development tools.

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: MS Access

July 10th, 2012

VBScript – Determine Executable Path for Given Application

Have you been trying to develop a flexible method of determining the path to launch an application. You quickly learn that software developers, in some instances, make it indredibly difficult for us to create a simple static path for the executable directory.

For instance, say you create a database and then deploy it, you can’t just use a static path to launch it, like:

C:\Program Files\Microsoft Office\OFFICE 11\MSACCESS.EXE

because this will work only with Office 2003. What happens if your user is using 2007, 2010, 2000, …???

So what can one do? Well, it is possible to create a series of if statements, to check and see if a 2010 folder exists, if not, then check for 2007, and so on, but this once again rellies on a series of static enties that you make as a developer. For typical installation this would work, but what about that special user that didn’t install Office in the typical location? Your script will fail, no and if or about it!

So What Can One Do?! Actually, the solution is quite straight foward and works for most every program. All we need to do is check with the registry as to where the program was installed! That’s right, let’s just ask the computer, “where did you put the application”? The beauty is in its simplicity.

For MS access, we only need to do the following

Dim WSHShell
Set WshShell = Wscript.CreateObject("Wscript.Shell")
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\")

Obviously, instead of sending the result to a msgbox we could assign it to a variable and use it to actually launch a database.

What other programs can we use this technique with? You name it: Excel, Groove, Internet Explorer, FireFox, Info Path, MS Access, One Note, Outlook, Power Point, Word, … and the list goes on. Below is but a few examples of other programs.

Dim WSHShell
Set WshShell = Wscript.CreateObject("Wscript.Shell")
'MS Excel
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe\")
'Groove
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\GROOVE.EXE\")
'Internet Explore
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\IEXPLORE.EXE\")
'Info Path
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\infopath.exe\")
'MS Access
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\")
'MS One Note
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\OneNote.exe\")
'MS Outlook
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\OUTLOOK.EXE\")
'PowerPoint
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\powerpnt.exe\")
'MS Word
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\Winword.exe\")
'WordPad
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\WORDPAD.EXE\")
'Write
MsgBox WSHShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\WRITE.EXE\")

So you can simply test out other application by using the appropriate executable or use regedit navigate to the “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths” key and review the list of application present on your system.

But as you can see, with but 3 little lines, we can have a flexible way to get the path to our executable. No more guessing, no more messing!

Share and Enjoy

Category MS Access General Information, VBScript & Batch Files | Comment No Comments »

Tags: VBScript

April 16th, 2012

MS Access – Decompile a Database

Decompiling an Access Database

As a developer, it becomes necessary to decompile your database from time to time. The frequency depends on a multitude of factors, but at the very least, one should decompile one’s application prior to release. Decompilation is yet one more step a develpper has to take to ensure their database/code is clean and optimal for their end-users. Also, keep in mind that the best approach is to decompile the database on the development machine and then recompile on the end-user machine. This ensures that the database is compiled using the end-user’s libraries minimizing any surprises when put into production.

Before going any further on this subject, let me emphasize once more the importance of making a backup of your database prior to performing a decompile of your database!

One-time Decompile

The MSACCESS.EXE command line accepts several command line switches, one of which is to allow decompiling your database. There is no other means to decompile a database. So one merely needs to create a shortcut including the appropriate command line switch in order to decompile any given database. The basic syntax would be:

"FullPath\MSACCESS.EXE" "FullPathAndDbNameWithExtension" /decompile

Examples:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile

Reusable decompilation method

On the other hand, it become tedious to create a shortcut for each database you create/manage and as such a more automated method may be a good idea to implement.

Using Windows Explorer (etc.) navigate your way to your MSACCESS.EXE and the copy the file.
Navigate to the %APPDATA%\ Microsoft\Windows\SendTo\ Folder
Right-Click within the folder and select Paste shortcut from the context menu
Rename the shortcut as you wish, for instance MSACCESS Decompile
Right-Click on the newly created shortcut and select Properties from the context menu
On the Shortcut tab, edit the Target control by adding /decompile to the existing value.
You should end up with something along the lines of:

"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile

Click Ok
Close windows explorer.

Now whenever you wish to decompile a database you need only right-click on the Access database in Windows Explorer and select “Send To –> MSACCESS Decompile.

Special Note

Since after decompiling we always perform a compact of the database, you can perform both in one step if you’d like by appending a /compact to your /decompile command. This is not obligatory by any means. Personally, I prefer the granularity of performing each step myself, but should you wish to automate things a little more you’d do something along the lines of:

"C:\Program Files\Microsoft Office\Office\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact

"C:\Program Files (x86)\Microsoft Office\Office12\MSACESS.EXE" "C:\Databases\Test\Database1.mdb" /decompile /compact

"C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile /compact

Do note that if you do implement this variation for the reusable decompilation method, you’d probably be best to also change the name attributed to the command in step 4. to MS Access Decompile and Compact

Proper Steps to Follow when Decompiling an Access Database

I found the following instructions from David Fenton, and thought they fit right into this subject.

Backup your database.
Compact your database.
Decompile your database (per either method listed above).
Close that instance of Access.
Open a new instance of Access and open the database you just decompiled, but BE SURE YOU BYPASS ALL STARTUP CODE (i.e., hold down the shift key). If you don’t do that, then you might as well go back to step 3 and try again, since if the startup code runs, your code will recompile before you are ready to do so.
Compact the decompiled database (and be sure you hold down the shift key so that it bypasses the startup code; see #5).
Open the VBE and on the Debug menu, choose COMPILE [name of project].
On the file menu, save the project.
Compact again.

Why are all these steps necessary?

Because you want to not just decompile the VBA, you want to make sure that all the data pages where the compiled p-code was stored are completely discarded before you recompile.

I also recommend:

In the VBE options, turn off COMPILE ON DEMAND
In the VBE, add the COMPILE button to your Toolbar.
Compile often with that button on the toolbar, after every two or three lines of code.

Last of all, read Michael Kaplan’s article on the subject to understand it better.

Share and Enjoy

Category MS Access, MS Access General Information, MS Access VBA Programming | Comment No Comments »

Tags: MS Access MS Access VBA Programming

February 14th, 2012

MS Access – Button Picture – doesn’t support this format … or the file size is too big

I came across the following post and wanted to see what the issue was since I routinely use Irfanview myself (it is a great, free no strings attached piece of software).

So I contacted the creator of Irfanview directly regarding the error, and he was able to pinpoint the exact nature of the problem. As it turns out, the ico files created by Irfanview are just fine. The issue is that MS Access would seem to expect a specific type of ico file (and doesn’t tell anyone this).

Irfanview creates a 24BPP (with no alpha layer) ico file while MS Access seems (based on my testing and returned information from Irfan) to expect a 32BPP with an alpha layer ico file.

So for anyone else ever seeing this this of error. An ico is not an ico in the world of MS Access. It is picky and requires a specific type of ico file. So just be sure of the type of file your are creating is a 32BPP with an alpha layer ico and you should have no problems using them as a picture for a button. Alternately, instead of having such issues using a ico file, use BMPs instead. I personally don’t like BMPs as they aren’t used in any other programming (always jpg, gif, png, ico) so I will stay with ico so I can reuse them easily where I please. It would be nice if MS would integrate common image files into the application, but that is out of my hands (if it were up to me many issues like this would have been resolve a long time ago!).

Hopefully this will help someone out.

Share and Enjoy

Category MS Access Forms, MS Access General Information | Comment No Comments »

Tags: MS Access MS Access Forms

January 21st, 2012

MS Access 2007-2010 Ribbon Development

A while back, ok several years ago, I took a look at the new (at the time it was new) ribbon. I was interested in porting my Custom Toolbars into the new and improved format. At the time I spent hours trying to digg up information on the subject.

To put things bluntly, MS messed up good on the implementation!

This is not to say that the ribbon is not a powerful tool and all the rest of that.

That said, it has been implemented in what I consider an incomplete form. MS provides no tool, to develop custom ribbons, no built-in callback routines…, no access to the ribbon built-in images, and so many more flaws I don’t even know where to start! It is a mess.

Then they still support old custom toolbars, but you have no way to edit, delete or work with them directly. You have to revert back to using 2003 or prior, or develop your VBA own code to interact with them.

The whole situation smells bad.

So what is one to do. First off, you have to realize, that the entire Office suite Ribbon is fully customizable and to work with it you can use a Custom UI Editor, EXCEPT for MS Access. That’s right, as usual, MS Access is in a category of its’ own. What a surprise!

Firstly, get ready to do a lot of reading, before you are even ready to tackle creating a ribbon. Here are a few places to start:

www.accessribbon.de
http://www.andypope.info/vba/ribboneditor.htm
http://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx
http://msdn.microsoft.com/en-us/library/bb187398.aspx

http://www.rondebruin.nl/ribbon.htm

For MS Access, try http://www.ribboncreator.de/en/ The ribbon creator is a graphical interface to create the required XML and VBA code. Basically, what MicroSoft forgot to include in its’ own software!!! At the very least it will speed up development and you can tweak what it generates.

All in all, the ribbon, because of the way MS has choosen to implement a half baked technology, is a miserable failure (from the developer’s perspective)! Actually, even from the end-user’s perspective also. Where I used to be able to load 6 toolbars containing 50 icons (or more) where I had all the commands I needed at my finger tips, I now have 1 ribbon containing 10-20 commands. You end up spending your time flipping back and forth, all day, between tabs! It is never ending and teadious after a while. This is MS’ idea of empowering the end-user? Come on! But hey, it looks cool dude! This is the best that the best minds at MS could come up with; seriously!!!

That said, I believe MS has other plans for the ribbon, probably a way to introduce the end-user to the new layout, flow to be expected in future releases of their OS or other programs. Let use not forget the fact that MS is playing a major game of catchup with MAC.

Share and Enjoy

Category MS Access, MS Access General Information, MS Office | Comment 5 Comments »

Tags: MS Access MS Excel MS Word

December 15th, 2011

MS Access – Splitting and Deploying Your Database – Part 2

In my previous post entitled Splitting and Deploying Your Database, I elaborated on the critical importance of splitting your database before deploying it to your end-users. That said, many people post questions regarding securing their applications from their end-users to minimize their ability to mess around with the database, mainly fool around directly with the tables. In this post I will elaborate on a few possible step you can take as a developer to best secure your database from what I qualify of ‘dangerous’ users.

There are a few things that you can do to try and harden your database against your users. In 2 instances, you have the ability to take some steps using programming to secure your app. In the 3rd, you can deploy your application in a secured method. Let’s examine each.

Hide Object Pane & Disable SHIFT Bypass

As a developer, you can do your best to make it as difficult as possible for any user to gain access to any of your database objects (tables, queries, forms, reports, …) to pose a threat in the first place. To do this you need to:

Create an autoexec macro the uses RunCode to execute a VBA procedure at startup
In your startup procedure add in the following code

If SysCmd(acSysCmdRuntime) = False Then
    DoCmd.SelectObject acTable, , <strong>True</strong>
    DoCmd.RunCommand acCmdWindowHide
End If

This code will hide the Access object browser (the pane that lists all the database objects: tables, queries, forms, reports, …). If they can’t see them, then they can’t mess around with them!

Since any knowledgeable user knows that they can bypass any autoexec macros by holding the SHIFT key at startup, we need to disable the shift bypass capacity. Now there is no point in rehashing this subject, so please refer to: http://access.mvps.org/access/general/gen0040.htm

Convert to MDE/ACCDE

One more step you can take is to convert your database (mdb/accdb) to an (mde/accde) format. By doing so you lockout the user’s access to the all the VBA code. Furthermore, this creates a compiled version of the database which mean it should be optimized for use. Win, win. Just remember you cannot do development on an (mde/accde), so keep your original file (prior to conversion) for further development.

Deploy Your Database Using Runtime

The last thing you can do to restrict your users and limit their ability to run amuck in your database is to deploy your application using MS Access’s runtime version. Instead of giving your user the full blown MS Access application, only install the free runtime version. Unless, your user needs to do development in the database, there is no need for them to have the full version of MS Access. By doing this, your user will not be able to edit any of the database objects! They will only have access to whatever you have developed and given them access to. Also, note that when deploying using the runtime version ensure you put in place error handling throughout all of your VBA procedures, otherwise when an error is raised, the application will blatantly crash. You can freely download and install the runtime version of MS Access directly from Microsoft’s website at:

MS Access Runtime 2007 -> http://www.microsoft.com/download/en/details.aspx?id=4438
MS Access Runtime 2010 -> http://www.microsoft.com/download/en/details.aspx?id=10910

Share and Enjoy

Category MS Access, MS Access General Information | Comment No Comments »

Tags: MS Access

November 29th, 2011

MS Office – Executable File Versions

It can sometimes be necessary to determine the version of the program you are working with in VBA and I have been unable to find a comprehensive listing. Microsoft themselves have scattered this information over numerous pages?! Here is what I have compiled myself thus far (Office 2000 through Office 2010 SP1).

Application Name	Executable File	2000	2000 SP1	2000 SP2	2000 SP3	2002	2002 SP1	2002 SP2	2002 SP3	2003	2003 SP1	2003 SP2	2003 SP3	2007	2007 SP1	2007 SP2	2007 SP3	2010	2010 SP1
Microsoft Office	mso.dll					10.0.2627.01	10.0.3520.0	10.0.4330.0	10.0.6626.0	11.0.5614.0	11.0.6361.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6213.1000	12.0.6425.1000		14.0.4760.1000	14.0.6023.1000
Microsoft Access	msaccess.exe	9.0.2720	9.0.3821	9.0.4402	9.0.6926	10.0.2627.1	10.0.3409.0	10.0.4302.0	10.0.6501.0	11.0.5614.0	11.0.6361.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000	12.0.6606.1000	14.0.4750.1000	14.0.6024.1000
Microsoft Binder				9.0.2702	9.0.2702
Microsoft Excel	excel.exe	9.0.2720	9.0.3821	9.0.4402	9.0.6926	10.0.2614.0	10.0.3506.0	10.0.4302.0	10.0.6501.0	11.0.5612.0	11.0.6355.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6214.1000	12.0.6425.1000	12.0.6611.1000	14.0.4756.1000	14.0.6024.1000
Microsoft FrontPage	frontpg.exe	4.0.2.2720	4.0.2.3821	4.0.2.4426	4.0.2.6625	10.0.2623.0	10.0.3402.0	10.0.4128.0	10.0.6308.0	11.0.5516.0	11.0.6356.0	11.0.7969.0	11.0.8173.0
Microsoft Groove	groove.exe													12.0.4518.1014	12.0.6211.1000	12.0.6421.1000	12.0.6600.1000	14.0.4761.1000	14.1.6009.1000
Microsoft InfoPath	infopath.exe									11.0.5531.0	11.0.6357.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6214.1000	12.0.6413.1000	12.0.6606.1000	14.0.4763.1000	14.0.6009.1000
Microsoft OneNote	onenote.exe									11.0.5614.0	11.0.6360.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6415.1000	12.0.6606.1000	14.0.4763.1000	14.0.6022.1000
Microsoft Outlook	outlook.exe/outlib.dll	9.0.0.2711	9.0.0.3821	9.0.0.4527	9.0.0.6627	10.0.2627.1	10.0.3416.0	10.0.4024.0	10.0.6626.0	11.0.5510.0	11.0.6353.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6212.1000	12.0.6423.1000	12.0.6607.1000	14.0.4760.1000	14.0.6025.1000
Microsoft PowerPoint	powerpnt.exe	9.0.2716	9.0.3821	9.0.0.4527	9.0.6620	10.0.2623.0	10.0.3506.0	10.0.4205.0	10.0.6501.0	11.0.5529.0	11.0.6361.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6425.1000	12.0.6600.1000	14.0.4754.1000	14.0.6026.1000
Microsoft Project	winproj.exe					10.0.2915.0	10.0.8326.0			11.0.5614.0	11.0.6707.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000		14.0.4751.1000	14.0.6023.1000
Microsoft Publisher	mspub.exe					10.0.2621.0	10.0.3402.0	10.0.4016.0	10.0.6308.0	11.0.5525.0	11.0.6255.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000	12.0.6606.1000	14.0.4750.1000	14.0.6026.1000
Microsoft SharePoint Designer	spdesign.exe													12.0.4518.1014	12.0.6211.1000	12.0.6423.1000		14.0.4750.1000
Microsoft Visio	vision.exe/vislib.dll					10.0.525	10.1.2514	10.2.5110		11.0.3216.5614	11.0.4301.6360	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6423.1000		14.0.4756.1000	14.0.6022.1000
Microsoft Word	winword.exe	9.0.2720	9.0.3821	9.0.4402	9.0.6926	10.0.2627.0	10.0.3416.0	10.0.4219.0	10.0.6612.0	11.0.5604.0	11.0.6359.0	11.0.7969.0	11.0.8173.0	12.0.4518.1014	12.0.6211.1000	12.0.6425.1000	12.0.6612.1000	14.0.4762.1000	14.0.6024.1000

You can also download a copy in csv format so you can import it into the program of your choice by clicking here.

Also, note that some of this information is impossible to find on any official website (but then again, why would I be writing such a post if it were!) so there could be mistakes. This is based on what I could find online and what I managed to piece together by looking over various computers. If you find mistakes, please use the contact form to send me an e-mail and I will make the necessary corrections promptly.

Here are a few of my references that I used to compile this list:
Office 2000
Office 2002 (Office XP)
Office 2003
Office 2007
General info on how to retrieve the Executable’s build number

Share and Enjoy

Category MS Access, MS Access General Information, MS Office | Comment No Comments »

Tags: MS Access MS Excel MS Word

October 4th, 2011

MS Access – AutoNumber Field

We tend to see numerous questions relating to MS Access Autonumbers. Specifically, regarding autonumber not following sequence, skipping sequence.

Regardless of how one might interpret what is written in the help files, AutoNumbers should never, ever, ever, be relied upon as a sequential number. Nor should they ever be used/displayed to the end-user. The simple fact of the matter is that AutoNumbers are merely unique identifiers for each record. Even when set to be incremental, the AutoNumber can be indexed even though no record was actually inserted into the table. This is not a bug, this is simply the way Access was developed.

So what does one do if they require a sequencial number to attribute to each record. The solution is actually very simple. Create a new field in your table and then you can used an equation such as =Dmax(…)+1 to generate the next number in your sequence. But at the end of the day, if you want a sequential number that will not jump sequence and can be faithfully relied upon, you have to create it yourself!

Share and Enjoy

Category MS Access, MS Access General Information, MS Access Tables | Comment No Comments »

Tags: MS Access MS Access Tables

July 21st, 2011

MS Access – Tables – Where to Create Relationships? In the Front-End or Back-End?

Now here is a question I had myself many years ago and could never find a proper answer to! And yet it is such a basic element and such an important one at that!!!

So when one creates a split database (which they all should be), where do you create the tables relationships exactly? Do you create the MS Access table relationship within the Front-End or the Back-End?

The short answer is, in the Back-End. Here are a few explanation from a couple fellow MVPs taken from a recent forum question.

The relationships need to be established in the backend. In fact, you can build a diagram in the frontend but for the backend tables, the referential integrity will not be enforced if the relationships aren’t in the backend — Bob Larson, Access MVP

If you create relationships in the front end the only thing it achieves is that it determines the default joins types when you create a query in design view. To ensure data integrity through enforced relationships they must be created in the back end. So, the recent advice you were given is wrong. You can if you wish create them in the front end in addition to, but certainly not in place of, those in the back end. — Ken Sheridan

What is important to understand here is the fact that you should always create your table relationships in the Back-End of your database. That said, as Ken stated it can be useful to merely recreate them within the Front-End as well to simplify Query building, but then this then incurs extra overhead when the database is modified (Now you have to update the relationships in both locations). The other time when you might create MS Access table relationships within the Front-End of your database is because you have lookup table, reference tables in the Front-End. In such a case, obviously you can’t create the necessary table relationships in the Back-End since the tables don’t exists there. As such, you’d create the necessary relationships directly within the Front-End to ensure referential integrity.

Share and Enjoy

Category MS Access General Information, MS Access Tables | Comment No Comments »

Tags: MS Access MS Access Tables

April 30th, 2011

MS Access – JET SHOWPLAN

I came across an article about optimizing queries using a hidden JET tool call SHOWPLAN which basically generates a showplan.out file (which is merely a text file) which elaborates the method used by Access to execute the query. With this information it is possible to perform optimizations, determine which fields require indexing…

I'm not going to rewrite the article, if the subject interests you, then simply look at the source article.

That said, I did want to try and add a little more information for anyone trying to get Showplan to work on Windows 7 (possibly Windows Vista – untested). To be able to use ShowPlan one must create a registry entry to enable it. In the original atricle, they indicate that the base registry key is found at:

\\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\4.0\Engines

this may be the case for Windows XP, but if you are using Windows 7 (and I suspect Vista as well) you will not find this key. Instead look for:

\\HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines

The above mentioned article appears to have been moved to: Use Microsoft Jet's ShowPlan to write more efficient queries.

Share and Enjoy

Category MS Access General Information, MS Access Queries | Comment 1 Comment »

Tags: MS Access MS Access Queries ShowPlan

March 13th, 2011

FMS Total Visual SourceBook Review

FMS Total Visual SourceBook

What I wouldn’t have given to be aware of this add-in when I was starting out as a developer! Seriously, this add-in would have saved me hundreds, if not thousands, of hours searching online, posting to forums, to find out how to code what I needed to do. Beyond which, it provides the user with a standardized set of procedures, instead of trying to piece together countless routines found here and there as you search online.

Just yesterday, I needed a particular routine and instead of searching online, as I always have done until now, I opened the TVSB, performed a quick search, exported the appropriate code into my module and was back at work in a matter of 1-2 minutes, if that!

So what did I think of the TVSB?

Cons:

I wish it could be somehow directly integrated within the VBE as done with certain other add-ins rather than a separate popup application. Have some type of integrated toolbar with a drop down category/procedures/… select the procedure and BAM there is. AND, I’m not saying it is hard to export the procedures from the SourceBook the way it is currently setup. That said, even though it would be nice, I myself am not sure how it could be accomplished.
My other issue is that their code uses Early biding which I try to avoid normally as it can causes reference issues. So their code is a nice starting point, but I would convert most of the classes, procedures,… into late binding for my own purposes. Over the course of several years, I have learnt that Late Binding avoids reference issues and this outways (in my opinion) any performance benefits Early Binding presents. At the end of the day, each developer has their own opinion and experience with regards to this aspect of programming, so feel free to make up your mind on this aspect yourself. To learn a little bit more about the pros and cons of Early Binding vs. Late Binding take a look at Early vs. Late Binding
from the Word MVP site, it is a short overview of the issue.

Pros:

Easy to install
Can be integrated to work in a team environment (untested)
Easy to navigate and work with
It is very intuitive
Came with a user manual! Just this to me put this application above most others!!! Although, in this case, a manual is not necessary.
The code itself, is well categorized so you can find things quite easily just by noising around.
Effective search tool enable one to quickly search through the repository.
Extendable. You can add your own code (procedures, modules, …) to the repository so you can build upon what is already there.
Good export utility (export directly into your module, to a file, …)
Customizable – You can configure the Error Handler and other elements to suit your programming methodologies.

Put simply, the FMS Total Visual SourceBook (TVSB) is a simple to use, powerful work tool that can easily accelerate the development process of any developer and give you a leg up on your competition.

Share and Enjoy

Category MS Access General Information, MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming, Product Reviews | Comment No Comments »

Tags: MS Access VBA Programming Product Review VBA

January 17th, 2011

MS Access – VBA – The Save Operation Failed

I was recently working on a database of mine which has been in production for over 4 years now and all of a sudden it started giving me the error: “The Save Operation Failed”. Nothing like a nice obscur error message from the VBA/VBE!!!

I tried the normal approaches: Compact and repair, Decompile/Recompile, … None worked for me.

I took a look at Microsoft’s Knowledge Base and found an article, ACC97: Error “The Save operation failed” When Saving a Module, for Access 97 (as a lot of the information can be used in furture version, I read it over). Sadly, the article is pretty much useless.

In the end, the solution, for me, was to start a new database and import all of the database objects (File -> Get External Data -> Import) from the database giving me the error message. Setup the startup properties… and everything was in working order again!

I hope this helps someone else in the same perdicament!

Share and Enjoy

Category MS Access, MS Access General Information, MS Access VBA Programming | Comment No Comments »

Tags: MS Access MS Access VBA Programming

January 6th, 2011

MS Access – VBE – Plug-Ins, Add-Ons, Etc

One thing any good worker will tell you is that you must have the right tools to do the job. Computer programming, MS Access database development, is no exception to this rule! I thought I’d list a few add-ins, plug-ins, etc. that I have come across, or heard of. This is an unbiased listing and I have no link to any of the vendors, nor have I even used all of them. I am simply trying to regroup them to help you find them, and you can judge their usefullness on your own. In a future post, I will examine the 2 or 3 that I use myself and find very useful.

Free Ones
MZ-Tools
Smart Indenter
V-Tools

Ones you have to pay for
Find and Replace (30 day evaluation then you have to register/pay)
FMS Inc. Tools — FMS offers a multitude of various tools worth reviewing, including: Total Visual Source Book and Total Access Admin

A Few More Untested Add-ins (Update 2011-Mar-09)

Various utilies by Bill Mosca (Access MVP)
ACCESS Dependency Checker
http://www.4tops.com/query_tree.htm
http://www.4tops.com/ms_access_vba.htm
Compare Em – Compare 2 database to identify the differences and generate the necessary code to make the updates.

Share and Enjoy

Category MS Access General Information, MS Access VBA Programming, MS Excel VBA Programming, MS Office, MS Word VBA Programming | Comment No Comments »

Tags: MS Access MS Access VBA Programming VBA

October 29th, 2010

MS Access – Running Access on a MAC

Ever needed to run an MS Access database on a MAC? It can be done.

One solution, is to install Parallels Desktop on the computer. This enables one to run Windows software directly on a MAC. Once it is installed, you then would install the OS of your choice and the MS Access. So you need valid OS license and a valid MS Access license or use the free run-time version.

That said, at $80 for parallels and then another $200+ to get a copy of windows this starts to become an expensive endeavour if it is only to use MS Access! Never the less, it is a viable option for those looking for a solution to a problem.

Share and Enjoy

Category MS Access, MS Access General Information | Comment No Comments »

October 28th, 2010

MS Access – Running Access on Linux/Ubuntu

Have you ever had a client throw you the nice curveball of wanting to run an MS Access database on a Linux OS? Well, there is a workable solution.

You can install Wine on most Linux distributions. You can check out their website at http://www.winehq.org/download. You can also check out their brief page regarding MS Access at http://appdb.winehq.org/objectManager.php?sClass=application&iId=12

Overall, this seems to be a very good option for older versions, and slightly less reliable for the newer version (2007) with no data regarding 2010 compatibility. Depending on you needs, this is definitely worth checking out and the price is right, it’s free (you have to love open-source)!

Share and Enjoy

Category MS Access, MS Access General Information | Comment No Comments »

October 18th, 2010

MS Access – Make Tables Read-Only

Now here is an interesting question!

My first reaction to such a question is why? Since proper database design implicitly prohibits the users from ever directly accessing the database tables, this should never truly be an issue for any developer. But then again, as I have learnt of the course of the years, there are so many exceptional cases…

A Few Solutions to Make a Table Read-Only

If you are using an mdb format, then you could simply implement User-Level Security (ULS) and simply apply the proper permissions to make your desired tables read-only to your users.
A more general solution, and one that would also work in all versions of MS Access, would be to migrate the tables you wish to be in Read-Only mode, into a second back-end file and then set the file attributes to Read-Only. Then link the tables to your front-end.

Share and Enjoy

Category MS Access General Information, MS Access Tables | Comment No Comments »

October 12th, 2010

MS Access – Trig Functions

Have you ever needed to use trig functions in your database?

You may have noticed that although MS Access does offer basic trig functions:

Sin
Cos
Tan
Arctangent (atn)

it does not offer any of the advance, ‘Derived Math Functions’, such as:

Secant
Cosecant
Cotangent
Inverse Sine
…
Hyperbolic Sine
…
Inverse Hyperbolic Sine
…

As a developer you have one of two options:

Create, or find, a function to replicate these functions
Utilize Excel’s trig functions from within you database

Create, or find, a function to replicate these functions

If you simply lookup the term ‘Derived Math Functions’ in the VBE’s help file you will find all the necessary information to build your own custom functions. That said, why not simply benefit from the fact that others before you have already done this work for you and simply perform a quick Google search to locate and existing module with these functions. For instance:

Utilize Excel’s trig functions from within you database

If you know that the database will be utilized on a computer that also has Excel installed on it, why not simply use it’s powerful library of trig function! Nothing could be easier to do. Simply use a procedure such as the one presented below

'---------------------------------------------------------------------------------------
' Procedure : Atanh
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Calculate the Inverse Hyperbolic Tangent by using Excel's built-in
'             function
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' x                 Value in Rads
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' Atanh(-0.9)       Gives -1.4722...
' Atanh(0)          Gives 0
'---------------------------------------------------------------------------------------
Function Atanh(x As Double) As Double
'This procedure requires a reference be set to the Microsoft Excel xx.x Library
On Error GoTo Error_Handler
    Dim oXls    As Excel.Application
 
    Set oXls = New Excel.Application
    Atanh = oXls.WorksheetFunction.Atanh(x)
 
Error_Handler_Exit:
    On Error Resume Next
    oXls.Quit
    Set oXls = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: YourModuleName/ListDbTables" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Share and Enjoy

Category MS Access General Information, MS Access VBA Programming | Comment No Comments »

Tags: MS Access VBA Programming Trig

September 15th, 2010

Launch/Open an MS Access Database Using a VBScript – Part 2

In my previous post launch-open-an-ms-access-database-using-a-vbscript I went over the basic concept of using a VBScript to launch a database.

In this post I would like to demonstrate the true power of VBScript for this type of application.

Say, you are a developer within a structured company network environment and you want to standardize the front-end setup on your users computers without having to sit down at each of their computers, one by one. Well, VBScript to the rescue!

The script below may seem scary, but really isn’t. It start by determining the user’s My Documents location. This is where I have chosen to place the Front-End application of the database. You could always alter this aspect, but it has served me very well in numerous routines. Once it has determine the location it determines the location of the VBScript itself (which will be installed with the Master copy of the Front-end). As such, it copies the Master copy to the user’s My Documents. Then it determines the location of the msaccess.exe required to launch the database and then finally launches it!

Once again, I do hope this helps someone out there as I found it hard to come across this information several years ago when I needed it.

'*******************************************************************************
'Date:		2008-05-27
'Author:	Daniel Pineault / CARDA Consultants Inc.
'Purpose:	This script should be located on a network share in the same
'		directory as the Front-End which it will automatically copy
'		to each user's MyDoc\Subfolder\ and then launch
'		Give your users a link to this script and it will do the rest
'Copyright:	You are free to use the following code as you please so long as
'		this header remains unaltered.
'Revision:	2008-05-27   Initial Release
'*******************************************************************************

	Const MY_DOCUMENTS = &H5&
	Const PROGRAM_FILES = &H26&
 
	Dim objShell
	Dim objFolder
	Dim objFolderItem
	Dim objNetwork
	Dim objFSO
	Dim objShellDb
	Dim DelFoldr
	Dim sMyDocPath
	Dim sProgPath
	Dim sVBSPath
	Dim sAccPath
	Dim sFrontEnd
	Dim sFolder
	Dim sSec
	Dim sUser
	Dim sPath
	Dim sComTxt
 
 
	'Specify the Fullpath and filename of the database to launch
	sFrontEnd = "test.mdb"	'Database name to open
	sFolder = "Databases"		'MyDoc subfolder where the
						'front-end will be copied to
	'If your database is secured by an mdw file specify it below, otherwise
	'leave its value blank
	sSec = "Security.mdw"
 
 
'Determine the location/path of the user's MyDocuments folder
'*******************************************************************************
	Set objShell = CreateObject("Shell.Application")
	Set objFolder = objShell.Namespace(MY_DOCUMENTS)
	Set objFolderItem = objFolder.Self
	sMyDocPath = objFolderItem.Path		'My Documents path
	sPath = sMyDocPath & "\" & sFolder & "\"'Path to front-end
	Set objFolder = objShell.Namespace(PROGRAM_FILES)
	Set objFolderItem = objFolder.Self
	sProgPath = objFolderItem.Path		'Program Files Path

'Determine path of this VBScript
'*******************************************************************************
	sVBSPath = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName) _
		     - (Len(WScript.ScriptName) + 1)))
 
	'Ensure lastest version of front-end is installed
	Set objNetwork = CreateObject("Wscript.Network")
	sUser = objNetwork.UserName			'User's network username
	Set objFSO = CreateObject("Scripting.FileSystemObject")
 
'Copy a network version of the Front-end to the MyDocs/SubFolder
'*******************************************************************************
	If objFSO.FileExists(sPath & sFrontEnd) Then
 
	Else
  		If objFSO.FolderExists(sPath) then
    			'Delete folder to perform cleanup of old version(s)
    			Set DelFoldr = objFSO.GetFolder(sPath)
    			DelFoldr.Delete
  		End if
  		'Create folder and then copy required file(s)
  		Set objFolderCreate = objFSO.CreateFolder(sPath)
  		objFSO.CopyFile sVBSPath & "\" & sFrontEnd, sPath & _
                  		sFrontEnd, OverWriteExisting
	End if
 
'Determine the location of the MS Access executable
'*******************************************************************************
	Set objShellDb = CreateObject("WScript.Shell")
	'Determine in which folder the Access executable is located
	If objFSO.FileExists(sProgPath &_
                     	     "\Microsoft Office\OFFICE11\msaccess.exe") Then
		sAccPath = sProgPath & "\Microsoft Office\OFFICE11"
	Elseif objFSO.FileExists(sProgPath &_
                                 "\Microsoft Office\OFFICE10\msaccess.exe") Then
  		sAccPath = sProgPath & "\Microsoft Office\OFFICE10"
	Elseif objFSO.FileExists(sProgPath &_
                                 "\Microsoft Office\OFFICE\msaccess.exe") Then
  		sAccPath = sProgPath & "\Microsoft Office\OFFICE"
	End if
 
 
'Launch database
'*******************************************************************************
	'Build the command to launch the database
	sComTxt = chr(34) & sAccPath & "\" & "msaccess.exe" & chr(34) &_
		  " " & chr(34) & sPath & sFrontEnd & chr(34)
	if isNull(sSec)=False AND sSec<>"" Then
		sComTxt = sComTxt & " /wrkgrp " & chr(34) & sVBSPath &_
			  "\" & sSec & chr(34)
		if isNull(sUser)=False AND sUser<>"" Then
			sComTxt = sComTxt & " /user " & sUser
		End if
	End if
 
	objShellDb.Run sComTxt 'Launch the database

Share and Enjoy

Category MS Access General Information | Comment No Comments »

Tags: VBScript

September 14th, 2010

Launch/Open an MS Access Database Using a VBScript

Have you ever tried to make a shortcut to launch/open an MS Access database and had an error returned because the Target string was too long? Or do you simply need more control/flexibility from your launching routine. In that case, a VBScript is the ideal way to go. Below is the most basic format that the VBScript can take. Simply alter the Script Variables, Save and execute!

I have made the following as flexible as possible so it can open simple database files, as well as security enabled database. It is simply a question of assigning values to the Script Variables or not.

I truly hope this helps you out!

'*******************************************************************************
'Date:		2008-05-27
'Author:	Daniel Pineault / CARDA Consultants Inc.
'		http://www.cardaconsultants.com
'Copyright:	You are free to use the following code as you please so long as
'		this header remains unaltered.
'Purpose:	Launch the specified access database
'Revision:	2008-05-27   Initial Release
'*******************************************************************************

	Dim sAcc
	Dim sFrontEnd
	Dim sSec
	Dim sUser
	Dim objShellDb
	Dim sComTxt
 
'Script Configuration Variable
'*******************************************************************************
	'Specify the Fullpath and filename of the msaccess executable
	sAcc = "C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe"
	'Specify the Fullpath and filename of the database to launch
	sFrontEnd = "D:\Main\My Documents\TestDb.mdb"
	'If your database is secured by an mdw file specify it below, otherwise
	'leave its value blank
	sSec = "C:\Databases\Security.mdw"
	'If your database is secured by an mdw file and you want to specify the
	'username to use specify it below, otherwise leave its value blank
	sUser = ""
 
 
'*******************************************************************************
'*******************************************************************************
'You should not need to edit anything below this point
'*******************************************************************************
'*******************************************************************************

 
'Launch database
'*******************************************************************************
	Set objShellDb = CreateObject("WScript.Shell")
	'Build the command to launch the database
	sComTxt = chr(34) & sAcc & chr(34) &_
		  " " & chr(34) & sFrontEnd & chr(34) 
	if isNull(sSec)=False AND sSec<>"" Then
		sComTxt = sComTxt & " /wrkgrp " & chr(34) & sSec & chr(34)
	End if
	if isNull(sUser)=False AND sUser<>"" Then
		sComTxt = sComTxt & " /user " & sUser
	End if
	objShellDb.Run sComTxt 'Launch the database

Share and Enjoy

Category MS Access General Information | Comment 6 Comments »

Tags: VBScript

Search

Pages

Recent Posts

Categories

Tags

Archive for ‘MS Access General Information’

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Decompiling an Access Database

One-time Decompile

Reusable decompilation method

Special Note

Proper Steps to Follow when Decompiling an Access Database

Share and Enjoy

Share and Enjoy

Share and Enjoy

Hide Object Pane & Disable SHIFT Bypass

Convert to MDE/ACCDE

Deploy Your Database Using Runtime

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

Share and Enjoy

So what did I think of the TVSB?

Cons:

Pros:

Share and Enjoy

Share and Enjoy

A Few More Untested Add-ins (Update 2011-Mar-09)

Share and Enjoy

Share and Enjoy

Share and Enjoy

A Few Solutions to Make a Table Read-Only

Share and Enjoy

Create, or find, a function to replicate these functions

Utilize Excel’s trig functions from within you database

Share and Enjoy

Share and Enjoy

Share and Enjoy

Recent Posts

Calendar

Archives