One new post and interview on Office.com!
Office.com has again asked me to write a guest post for them, click below to see the post:
Five Common Pitfalls When Upgrading Access to SQL Server
They were also kind enough to interview me:
Q&A with Access developer Juan Soto
Join me at the PAUG conference!
I’m really pumped about this years PAUG Access conference at Silver Falls. Not only do I again get to huddle with the brightest minds in Access development, both authors and other consultants, but I get to do it for three days in a wonderful, secluded area!
I’ll be presenting this year on how to use Access as a BI tool with SQL Server.
As of Sunday 2/19 there are only 8.5 rooms available, click here to register today!
Using FileStream with Access to deliver docs via The Cloud
Ben has completed the first phase of a two phase project using Access 2010, SQL Server in the cloud and FileStream; all coming together in one nice solution we would like to share with you.
Creating proposals using Access and Word
We were hired by an insurance company to create a solution that would use Access in creating sales proposals in Word. They were creating a large amount of complex word proposals using templates by hand and it would take the entire organization weeks to go through them all.
The Solution – multiple Word sections coming together in one Doc
Their proposal is a lengthy document with a wide range of different tables and verbage based on the type of proposal being created. Rather than use a handful of templates we decided to instead create sections that would come together in one final doc. We had one section for RX plans, another for dental, another for references, etc. Each section has it’s own set of tokens we would fill in from Access, as well as programming tokens to add tables, rows and other data needed for the proposal.
In Access we followed a similar methodology, with users putting together sections into a consolidated proposal using forms. We did it using a vertical column of values that would read from predefined field aggregates, therefore eliminating the need to add new columns as new tokens are added to the system.
For example, if a user added an Rx section they can pick from a wide range of predefined plans with values already negotiated with the supplier. Once the numbers are copied over from the standard plan users can customize the plan by changing allowable values.
SQL Server in the cloud
Our client has staff in the field that needed anywhere access and also wanted to avoid the hassles of backing up and maintaining SQL Server, so they decided to use our SQL Server in the cloud for Access solution.
SQL Server FileStream and Word Automation
Each proposal has a document center that allowed users to upload pdfs, spreadsheets and other related documentation regarding the proposal. Users can create multiple versions of the proposal and those also get stored in the document center. Since the app is used by users outside of the office via the web, we decided to take advantage of SQL Server’s great FileStream feature that allowed Access to easily upload and download files from the cloud to the user’s PC. It was our first time using this feature with Access and we where thoroughly impressed with how easy and reliable it is.
FileStream to the rescue
Think of a FileStream column as just another varbinary(MAX) column. The difference is that FileStream documents are stored outside the database in a controlled folder on the server. Advantages of this approach:
- Since documents are not stored in the database we avoid bloating, (great since SQL Express is limited to 11 Gigs)
- We developed a more granularized backup strategy, backing the database separate from all the binaries that are generated & saved.
- Because files are stored in server’s file system rather in the database, storage and retrievals can be more effective, in particular with files larged than 1 MB.
Versioning and Word Automation
Ben came up with a innovative solution to make sure Word documents changes were saved to the cloud with minimum user intervention. We wanted Access to remain in control of the Word doc at all times:
- We have Access binding to Word’s Application and Document events so it knows when the document is closed by the user and whether the user chose to save or discard the edits.
- We added a “version” property to each Word file created by the system, allowing us to compare the local version of the file with the version stored in the cloud.
- When a user requests a document from the doc center, Access will check if there is already a local copy of the file, get the version and compare it with the cloud version number. If the document on the server is newer, it gets downloaded and opened for the user, otherwise we just load the local copy.
- When the user closes the file we check if changes have occurred and if so, update the version number and upload it to the cloud.
Early binding required & Macro security considerations
In order to intercept and use Word events, Ben had to use early binding, (we prefer late binding for all of our needs), there is no way to late bind the Word objects and also receive events raised from those objects. That can be a problem when users may not have the same version of Word. For our case, it was acceptable as the client agreed to have everyone using the same version of Office. To avoid macro security alerts from having VBA in Word objects, all code for handling Word objects’ events were stored inside the Access application. In order for all of this to work Access has to be running at all times when interacting with documents. We added a safeguard where when Access is closed, users are prompted to close all managed Word documents (or cancel & review the edits).
SQL Server, Access, FileStream and Word – Better then a browser solution
We developed a cloud solution that you would be hard pressed to deliver via a browser, since I’m not aware of any way you can incorporate Word automation with a SQL Server database without using Access or .Net. I’m continually amazed how we continue to push the envelope with our favorite tool.
Interested in using this technology at your company? Call us for a great quote today!
Zip and Unzip from Microsoft Access VBA
On occasions, we have a need to zip files as part of our workflow within Access VBA. One sore point with zipping is that there’s really no simple way to zip or unzip files without depending on a third-party utilities. When you think about it, it is quite odd considering that zipping is built-in to Windows Explorer. (Reading between the lines, it seems to do with licensing constraints).
Click image to learn more about the book
Thankfully, Ron de Bruin has provided a solution which involves automating the Windows Explorer (aka Shell32). A Shell32.Folder object can be either a real folder or a zip folder so by manipulating a zip file as if it was a Shell32.folder, we can then use the “Copy Here” method of the Shell32.Folder to move the files in and out of a zip file.
As Ron has noted, there is a subtle bug when dealing with retrieving a Shell32.Folder via Shell32.Applications’ Namespace method. This code will not work as expected:
Dim s As String
Dim f As Object 'Shell32.Folder
s = "C:\MyZip.zip"
Set f = CreateObject("Shell.Application").Namespace(s)
f.CopyHere "C:\MyText.txt" 'Error occurs here
According to MSDN documentation, if Namespace method fails, the return value is a nothing and therefore we can get seemingly unrelated error 91 “With or object variable not set”. This is why Ron de Bruin uses a variant in his sample. Converting the string into a variant will work also:
Dim s As String
Dim f As Object 'Shell32.Folder
s = "C:\MyZip.zip"
Set f = CreateObject("Shell.Application").Namespace(CVar(s))
f.CopyHere "C:\MyText.txt"
Alternatively, you can choose to early bind by referencing Shell32.dll (typically in Windows\System32 folder). In VBA references dialog, it is labeled “Microsoft Shell Controls and Automation”. Early-binding is not subject to the string variable bug. However, our preference is to late-bind as to avoid any problems with versioning that may occur when running code on different computer with different operating systems, service packs and so forth. Still, referencing can be useful for developing & validating your code prior to switching to late binding & distribution.
Another issue we have to handle is that as there is only either “Copy Here” or “Move Here” method available with a Shell32.Folder object, we have to consider how we should handle the naming of files that will be zipped, especially when we are unzipping the files that potentially have the same name or should replace the original files in the target directory. This can be solved in two different ways: 1) unzipping the files into a temporary directory, renaming them, then moving them into the final directory or 2) rename a file prior to zipping so it will be uniquely named when unzipped and thus can be renamed. Option 1 is more safe but requires creating a temporary directory & cleaning up, but when you have control over what the target directory will contain, option 2 is quite simple. In either approach, we can use VBA to rename a file as simply:
Name strUnzippedFile As strFinalFileName
Finally, when using Shell32, we are essentially automating the visual aspect of Windows Explorer. So when we invoke a “CopyHere”, it’s equivalent to actually dragging a file and dropping it in a folder (or a zip file). This also means it comes with UI components which may impose some issues, especially when we are automating the process. In this case, we need to wait until the compression has completed before taking any further actions. Because it’s an interactive action that occurs asynchronously, we must write waiting into our code. Monitoring an out-of-process compression can be tricky so we’ve developed a safeguard that covers different contingencies such as compression occurring too quickly or when there is a delay between compression dialog’s progress bar is filling up and it is closing. We do this in 3 different ways; a) timing out after 3 seconds for small files, b) monitoring the zip file’s item count, c) and monitoring the presence of compressing dialog. The last part requires us to use WScript.Shell object’s AppActivate method because unlike Access’ built-in AppActivate, WScript.Shell’s AppActivate will return a boolean value which we can be used to determine whether activation was successful or not, and thus implicate the presence/absence of the “Compressing…” dialog without a messy API handling.
Sample usage
The complete code is given below. To use it, the code would look something like this.
'Create a new zip file and zip a pdf file Zip "C:\Temp\MyNewZipFile.zip", "C:\Temp\MyPdf.pdf 'Unzip the pdf file and put it in the same directory as the Access database Unzip "C:\Temp\MyNewZipFile.zip" 'Example of zipping multiple files into single zip file Zip "C:\Temp\MyZipFile.zip", "C:\Temp\A1.pdf" Zip "C:\Temp\MyZipFile.zip", "C:\Temp\A2.pdf" Zip "C:\Temp\MyZipFile.zip", "C:\Temp\A3.pdf" 'Unzipping a zip file with more than one file 'placing them into a networked folder and 'overwriting any pre-existing files Unzip "C:\Temp\MyZipFile.zip", "Z:\Shared Folder\", True
Here’s the complete Zip & Unzip procedure; simply copy’n'paste in a new VBA module and enjoy:
Private Declare Sub Sleep Lib "kernel32" ( _
ByVal dwMilliseconds As Long _
)
Public Sub Zip( _
ZipFile As String, _
InputFile As String _
)
On Error GoTo ErrHandler
Dim FSO As Object 'Scripting.FileSystemObject
Dim oApp As Object 'Shell32.Shell
Dim oFld As Object 'Shell32.Folder
Dim oShl As Object 'WScript.Shell
Dim i As Long
Dim l As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FileExists(ZipFile) Then
'Create empty ZIP file
FSO.CreateTextFile(ZipFile, True).Write _
"PK" & Chr(5) & Chr(6) & String(18, vbNullChar)
End If
Set oApp = CreateObject("Shell.Application")
Set oFld = oApp.NameSpace(CVar(ZipFile))
i = oFld.Items.Count
oFld.CopyHere (InputFile)
Set oShl = CreateObject("WScript.Shell")
'Search for a Compressing dialog
Do While oShl.AppActivate("Compressing...") = False
If oFld.Items.Count > i Then
'There's a file in the zip file now, but
'compressing may not be done just yet
Exit Do
End If
If l > 30 Then
'3 seconds has elapsed and no Compressing dialog
'The zip may have completed too quickly so exiting
Exit Do
End If
DoEvents
Sleep 100
l = l + 1
Loop
' Wait for compression to complete before exiting
Do While oShl.AppActivate("Compressing...") = True
DoEvents
Sleep 100
Loop
ExitProc:
On Error Resume Next
Set FSO = Nothing
Set oFld = Nothing
Set oApp = Nothing
Set oShl = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & _
": " & Err.Description, _
vbCritical, "Unexpected error"
End Select
Resume ExitProc
Resume
End Sub
Public Sub UnZip( _
ZipFile As String, _
Optional TargetFolderPath As String = vbNullString, _
Optional OverwriteFile As Boolean = False _
)
On Error GoTo ErrHandler
Dim oApp As Object
Dim FSO As Object
Dim fil As Object
Dim DefPath As String
Dim strDate As String
Set FSO = CreateObject("Scripting.FileSystemObject")
If Len(TargetFolderPath) = 0 Then
DefPath = CurrentProject.Path & "\"
Else
If FSO.folderexists(TargetFolderPath) Then
DefPath = TargetFolderPath & "\"
Else
Err.Raise 53, , "Folder not found"
End If
End If
If FSO.FileExists(ZipFile) = False Then
MsgBox "System could not find " & ZipFile _
& " upgrade cancelled.", _
vbInformation, "Error Unziping File"
Exit Sub
Else
'Extract the files into the newly created folder
Set oApp = CreateObject("Shell.Application")
With oApp.NameSpace(ZipFile & "\")
If OverwriteFile Then
For Each fil In .Items
If FSO.FileExists(DefPath & fil.Name) Then
Kill DefPath & fil.Name
End If
Next
End If
oApp.NameSpace(CVar(DefPath)).CopyHere .Items
End With
On Error Resume Next
Kill Environ("Temp") & "\Temporary Directory*"
'Kill zip file
Kill ZipFile
End If
ExitProc:
On Error Resume Next
Set oApp = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
End Select
Resume ExitProc
Resume
End Sub
An alternative using 3rd party resources
For those who would rather have more control over the process and do not mind including 3rd party utilities, Albert Kallal has a useful sample that allows a true programmatic access to the zipping/unzipping facility and requires no installment beyond copying the 2 DLLs file included wherever the front-end file goes. This is also useful if you do not want any UI components at all (e.g. allowing users to cancel the compression or click “No” to replacing file in a zip file).
6 Reasons why you should always use Access with SQL Server
If you’re having a hard time convincing management to use Access with SQL Server, then use this post as ammunition to get your point across:
Reason #1: Security
I’ve walked into situations where new clients are storing their customer’s credit card info, (including the three digits on the back), along with their billing address, home address and other confidential information in an Access backend. My reaction was to immediately notify them via a certified letter and as their consultant, I recommended that they cease and desist from using Access to store such highly personal information, and instead migrate to SQL Server. Otherwise I’m outta there. The fact is people can and will walk out with your Access backend on a thumb drive. This cannot happen with a SQL Server. Plus SQL Server integrates with Active Directory, has native encryption, and other security enhancements, Access doesn’t have this.
LIVE From Chicago Thursday January 26th, join us for Access KungFu, click here for more details.
Reason #2: Reliability
When you have users using Access backend, they’re all sharing a single data file and essentially cooperating and trusting one other to share nicely. If there’s a network issue or problem with one workstation, it could compromise the data file. This simply does not happen with SQL Server, it’s not affected by PC or Access crashes and is a more reliable environment for your data.
Reason #3: Processing Speed
When it comes to raw computational power you just can’t compare Access with SQL Server. If done right, your Access application should sizzle, pop and process large query operations with ease. SQL Server stored procedures and views are just no match for Jet.
Reason #4: Backups
Have you ever tried backing up an open Access file? Or have you come in the next day only to find your file corrupted and unusable? You can perform backups of your SQL databases while users are in the app.
Reason #5 Free
SQL Server Express 2008R2 will let you store up to 10 gigabytes of data and it is free to use. Of course it is not entirely free if you have to purchase a server, (it can be installed on a powerful workstation too), or you have to hire a professional Access developer to convert to SQL Server, but at least you don’t have to pay for this awesomeness. Don’t walk, run and get your copy today.
Reason #6: Web Enabled Access Databases (Data in the cloud)
SQL Server is the only way you can use full blown Access apps over a WAN, VPN or the Internet. Sure, you can also use Access Web Services, if you only need a simple app. But if you need to use all of what Access has to offer you can’t beat storing the data in the cloud and using Access anywhere in the world.
SQL Server and Access, Unbeatable
Next time someone says you don’t need SQL Server for your Access application I hope these six points and the posts linked in the article will help you convince them otherwise.
LIVE From Chicago Thursday January 26th, join us for Access KungFu, click here for more details.
Web training session January 26th live from Chicago!
I’ll be presenting at this month’s Chicago Access User’s Group. (www.CAUG.com) It will be broadcast-ed live via the web as I present my newest topic:
Access Kung Fu – Tap into your inner VBA Sensei
We will discuss three topics, TempVars, Enums and Classes.
- TempVars are a great way to store global variables in your application, use them everywhere: your code, queries, forms, and reports. We will discuss why they are a better alternative than a global module variable.
- Enums are a convenient way to detail related constants in your database, you will enjoy unparalleled functionality with your constants using this data type.
- Classes are what makes your app sizzle. If you’re not using classes then you’re not tapping into the best that Access has to offer, period. We will discuss what the classes are and will provide you with some great examples.
When and Where
There are two ways you can participate:
- In person: Go to CAUG.com and email Kevin Anderson to get on the guest list.
- Via Web: At 5:45 PM Central Standard Time on January 26th I’ll start the web conference, join.me/accessexperts, please plan on arriving before 6 PM so we can get you setup and working before the show. You can reach me via email or chat by using the join.me software if you’re having problems connecting to the web conference.
Don’t miss out on this great opportunity to learn more and expand on your Access skills!
How to save your SharePoint password in Windows
A poster asked me a wonderful question: “How do I get Access [linked to SharePoint] to remember my login credentials so I don’t have to login everytime?” The answer to the question isn’t simple as it should be and hence, I felt an article on the subject would be helpful for those who want to automate logging.
For people who have an account with Office365, remembering the account password is quite straightforward. Office365 has their own custom security and thus we get a different login form when we open an Access database that depends on Office365 (whether as a published web database or containing linked tables to lists on the site):
As you can see on the screenshot, you can simply check the checkboxes, “Remember Me” and “Keep me signed in” if you don’t want to get prompted for password again. Simple!
Great! But I’m not on Office365…
When you’re using a SharePoint server that is maybe hosted somewhere else or on your network, and SharePoint is using its default setting, you’ll see a login like this:
(on Windows XP)
(on Windows 7)
The important thing to note here is that it’s not an Access thing. Access is basically calling Internet Explorer to authenticate for you. So it’s the Internet Explorer setting what we want to work with, and hence why you wouldn’t find anything to do with managing authentication within Access.
“Remember Me” doesn’t mean what it says
A more accurate statement would be probably “Remember Me, if you’re allowed to”. People are understandably frustrated when they check the box then next time they still get challenged. What’s going on? Well, in name of security, Internet Explorer may not be permitted to save the password unless you make some changes to the settings. By default, it tries to automatically authenicate for Intranet, which isn’t appropriate in this case. Therefore you need to change it so that it uses username/password. To do this, we start with….
Start Menu -> Control Panel -> Internet Options:
On the dialog, select Trusted sites zone, then click the Sites button
On the dialog, clear the checkbox for “Require server verification…” and then enter the host name of your SharePoint server. You don’t need to enter the full address of where your Access application is published to or linked to, just the base address. So if your Access application has a SharePoint list linked to http://mySharePoint.local/sites/MySite, then you would just enter “mySharePoint.local”
Click “Close” to return to Internet Properties dialog, then click Custom Level.
Scroll all way to the bottom then select “Authenicate with current username and password”
Once you’re set the radio button, click OK, then OK again on Internet Properties dialog to close both dialogs and save the changes. Back on Control Panel, we go to User Account.
Note: From this point on, the steps diverge between Windows XP and Windows 7 — for those on Windows 7 scroll down further to see the steps for Windows 7.
On the Advanced tab, click Manage Passwords
Click the “Add” button.
On this page, enter the same server host name that you entered for sites dialog previously, then the username and password you use to log in to your SharePoint host.
Click OK, then Close, then OK. You’re done with establishing the identity. You can now go to your Access application, login in then check the “Remember Me” this time and you won’t be challenged next time you run your Access application.
For Windows 7 users
As mentioned the last part of saving passwords diverge when you get into User Accounts. In User Accounts, you would select Manage Credentials.
On Credential Manager, select “Add Generic Credentials”
You would then enter the same hostname of your SharePoint server that you entered in Sites for Trusted Sites and your username/password you use to log in.
At this point, you can now run your Access Application, log in and check the “Remember Me” and it’ll actually remember next time you run your Access application.
Caveats
The steps I have outlined here are essentially the same steps that one would take to remember the login for any other SharePoint function. If you have your SharePoint server hosted somewhere other than Office365, they will likely have specific instructions that may differ slightly in allowing you to use the “Remember Me” functionality. Thus, it may be good idea to consult with them first. Searching using “Remember Me SharePoint Internet Explorer” keywords usually will yield good results.
Furthermore, if you google a bit on the subject, you’ll find different methodologies. Some may recommend using Intranet zone instead of Trusted Sites zone in the Internet properties – there is indeed a subtle difference in behaviors between those two zones, so if you find that the above instructions does not work for you, give Intranet zone a try. This appears to be a version-specific issue for the Internet Explorer as well.
Can I use VBA to automate this?
This isn’t a final pronunciation but my guts says “No” on this subject. As mentioned, this is an Internet Explorer thing and Access needs to get the token after authentication, so while I believe there may be APIs that could allow you to simulate logging in, you’d be faced with problem of feeding that token to Access. One could argue this is a good thing since it makes it less likely that you’ll be hijacked and logged into someone else’s site. After all, saving passwords are big security issues (e.g. it may mean someone could just waltz in, sit in any one of the workstation and they have access to the data). You could use the information above to at least get your users set-up at the installation time, so it’ll be an one time thing.
If the article has helped or didn’t work, by all means, let us know in comments below!
Create temp tables in Access from SQL Server tables
If you’ve ever asked Access to do a join between a local Access table and SQL Server table (which is called a heterogeneous join) you may have experienced first hand how slow it can be to process results. The situation could be vastly superior if you can afford to download the SQL table as a temporary table to your Access front-end and then process the join. This post will provide you with an easy solution to download SQL Server data into Access using a subroutine you can call from code.
Pass Through Query + Make Table Query Combo (No Linked Table Approach)
There is no direct way to download SQL Server data into Access using code, you can’t use a ADODB recordset or command to download the data, since they only “see” the SQL Server side and don’t have exposure to your Access data. The technique I’m providing here will use a temporary pass-through query, then using it with a make table query to create the local Access table copy of your SQL data.
'Insert this constant in a public module:
Public Const conConnectionQry As String = _
"ODBC;DRIVER={SQL Server};SERVER=YourServerIPAddressGoesHere;" & _
"UID=YourUserIDGoesHere;PWD=DittoPassWord;" & _
"DATABASE=NameOfYourSQLServerDatabase"
Public Sub CreateLocalSQLTable( _
strTable As String, _
strSQL As String, _
Optional bolExportExcel As Boolean _
)
Dim qdf As DAO.QueryDef
Dim strQuery As String
10 On Error GoTo ErrorHandler
20 strQuery = "qryTemp"
30 DoCmd.Close acTable, strTable
40 DoCmd.DeleteObject acQuery, strQuery
50 DoCmd.DeleteObject acTable, strTable
60 Set qdf = CurrentDb.CreateQueryDef(strQuery)
70 With qdf
80 .Connect = conConnectionQry
90 .SQL = strSQL
100 .Close
110 End With
120 strSQL = "Select * INTO " & strTable & " FROM " & strQuery
130 CurrentDb.Execute strSQL
140 DoCmd.DeleteObject acQuery, strQuery
150 If bolExportExcel Then
Dim strFile As String
160 strFile = CurrentProject.Path & "\" & strTable & _
Month(Date) & Day(Date) & Year(Date)
170 If Dir(strFile) <> "" Then
180 Kill strFile
190 End If
200 MsgBox "Table " & strTable & " is ready for export to Excel"
202 DoCmd.TransferSpreadsheet acExport, , strTable, strFile, True
204 FollowHyperlink strFile & ".xlsx"
210 End If
ExitProcedure:
220 Set qdf = Nothing
230 Exit Sub
ErrorHandler:
240 Select Case Err.Number
Case 3376, 3010, 7874, 2059
250 Resume Next
260 Case Else
270 MsgBox Err.Description, vbInformation, "Error"
280 GoTo ExitProcedure
290 End Select
End Sub
The procedure takes three arguments:
- strTable: Name you wish to use for your local Access table
- strSQL: SQL statement used to retrieve data from SQL server. You can therefore extract multi-table joins into a local Access table.
- bolExportExcel: Boolean variable you pass-along if you wish to also extract the data into Excel
Example 1: Extract one table from SQL Server called tblCustomers
CreateLocalSQLTable "tblCustomers_SQL", "Select * from tblCustomers"
Notice how I appended the _SQL to my local Access table name. I will later delete all _SQL tables on program exit.
Example 2: Multi-table join extract
CreateLocalSQLTable "tblCustomers_SQL", "Select CustomerID, CustomerName, CustomerStatus from tblCustomers Inner Join tblCustomerStatus On tblCustomers.CustomerStatusID = tblCustomerStatus.CustomerStatusID", True
In the second example the code will also launch Excel with the extracted data, allowing my customer easy Access to data from SQL Server without the use of ODBC in Excel.
Add indexes and primary keys if needed
Once you’ve downloaded the data you may have a need to add indexes or primary keys to your local copy of the table, click here for a post that will walk you through that using Alter SQL commands in Access.
Another Approach using Linked Tables
If you have the SQL Server table already linked to your Access front-end you can extract the data using this procedure:
Public Sub CopyTableLocally(strTable As String)
Dim strSQL As String
10 On Error GoTo CopyTableLocally_Error
20 Application.Echo True, "Working on table " & strTable
30 DoCmd.DeleteObject acTable, strTable & "_SQL"
40 strSQL = "Select * Into " & strTable & "_SQL From " & strTable
50 CurrentDb.Execute strSQL
ResumeExit:
60 On Error GoTo 0
70 Exit Sub
CopyTableLocally_Error:
80 Select Case Err.Number
Case 3376, 3010, 7874, 2059 'Trying to delete an object that does not exist, continue
90 Resume Next
100 Case Else
110 MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CopyTableLocally of Module mdlGeneral"
120 Resume ResumeExit
130 End Select
140 Resume ResumeXit
End Sub
Some Caveats
- Don’t download too much data. There’s a good reason your data is on SQL Server. Pulling large amount of data only eats up network traffic and may cause contention issues or even deadlocks. I usually use this technique for small reference tables that I later use in a all local joins. If your data is small and the data rarely changes in the table you’re downloading then this code is for you.
- Beware of file bloating: Ideally you should keep your temp tables in a separate Access file, otherwise your front-end may start getting pretty large and require frequent compacting to slim it back down.
Thinking about hosting your SQL Server database on the web?
Hosting on the web is a hit or miss, you will find a wide range of providers, from dirt cheap hosting companies that will lump you in with hundreds of other databases, to premier firms that will rent you a server all for your self.
Many traps to avoid
If you’re not careful you may end up with a host of problems: Record locks preventing users from committing changes, speed issues, bottlenecks, and records you can’t edit, etc.
SQL Server Hosting for Access
We love the combination of SQL Server and Microsoft Access, it is an unbeatable combo, and if you need it to work on the web then you can’t go wrong with hosting your SQL Server database in the cloud, it’s fast, reliable and secure. If done right it may even be faster using the cloud than using a Access backend!
Given that most Access + SQL solutions are relatively small databases, you want to host your data with a reliable provider and someone who can provide you with specialized support to get your app on the web.
The Solution: Using AccessExperts.net hosting
If you are a frequent reader of this blog you already know the wealth of knowledge available here, it’s a treasure trove of techniques and advise to get your SQL Database to the web and all free. But if you host your database with us you will get much more:
- SQL Server 2008 R2 Hosting
- 15 Gigabytes of storage
- Encrypted communications between Access and SQL Server
- Encrypted file storage of your data
- Unlimited users
- Run jobs for a small fee
- Hourly and nightly backups
- Best of all: Personalized coaching to get your database on the web!
We want you to succeed – Free Coaching
Get one on one coaching every step of the way as you design, test, and deploy your database to the web, no other hosting firm offers more.
All of this for just $39 per month! And if you prepay for one year you willget one month free.
But wait! There’s one more thing…
FREE live tech support: we will assist you in trouble shooting connections between Access and your SQL database. Don’t fret on why your database is not connecting to the server, a solution is just one phone call away.
Ready to get started? Give us a ring at 773.809.5456 or contact us here.
Our favorite online small business tools
Our Access/SQL Server/SharePoint practice benefits from great tools on the web, some free and some not, and I’d like to share our experiences with you.
LogMeIn.com (Free) and LogMeIn Ignition (Subscription) for Windows
We usually use the Remote Desktop Connection when working with our client’s systems, but sometimes that’s not an option, so we ask them to install LogMeIn.com, which is free. The product has gotten better over the years and now offers support for multiple monitors, local printing and shared clipboards among other features. They offer a subscription product called LogMeIn Ignition for Windows, which allows us to easily login to all of our client computers without having to go to LogMeIn.com, definitively worth the price if you plan on supporting many people this way.
LogMeIn Ignition for iOS ($29.99)
I can’t tell you how many times I’ve had to pull over and help a client using my iPhone or iPad using the LogMeIn app. It’s not cheap at $29.99 compared to other $0.99 apps, but when you support clients for a living it’s indispensable. Buy it once and use it on all your IOS devices.
Join.Me (Free)
Our favorite support tool is now Join.me, it’s free and it’s also from LogMeIn.com but unlike LogMeIn it does not install a program for the viewer and it’s a small executable for the presenter. It’s what we use for all of our web conferences and has a free iOS app to boot. One big plus: It works even if both the presenter and the observer are both using Remote Desktop Connections, something we can’t say for other support tools out there. I’ve used the iOS app over 3G with the VOIP feature to speak with the client and it ran with no issue, you owe yourself to check this one out.
Toggl.com (Free)
We make our living with hourly consulting so it’s imperative we keep track of our time and it doesn’t get any easier with the free Toggl.com We keep track by project and client and best of all it keeps going even if we get the blue screen of death.
DropBox.com (Free)
We used to share large files using FTP, which was not intuitive for our customers. No more! Now we use DropBox for sharing files with clients and among ourselves, it doesn’t get any easier or more powerful than this. We can share a single folder with a client and anything we place in there gets automatically synced with them. We sprung for the $9.99 monthly plan since we also use it to share our code and store our server backups. We will most likely get the team package for $799 and get 1,000 GB of space, which we will use to backup our SQL Server databases over the web.
MozyPro.com (Subscription)
If you’re still backing up with tapes or backing up to a local drive you’re asking for trouble. How are you going to recover if there’s a fire, robbery or equipment failure? Backups over the web are the only way to go for piece of mind. I use the cheaper Mozy.com for my home PC and MozyPro.com for our servers. Space is dirt cheap at $0.50 per GB per month.
QuickBooks Online (qbo.intuit.com) Starts at $12.95/month
We have run our business on Quickbooks since 1994, but I got tired of the constant upgrade cycle, when Intuit introduced an online version we sprung at the chance to take our accounting to the next level. We signed up for the payroll service at Costco, (more on that below), and get further discounts through our accountant, (if they are not signed up get them to do so for the great discount). You may also get by with the minimum three logins, since your Accountant is free and so are any employees/consultants who are only entering their billing time. They use Toggl to track time and enter the entries into QB Online.
By far one of our best investments has been the Costco business membership. We get our business checks, payroll processing and a big fat check once a year from them since we buy as much as we can there.
Bank of America Credit Card Processing
If you don’t take credit cards you’re missing out on improved cash flow and making it easy for clients to pay you. Most of our business is now done through credit cards. Since we bank at BofA the funds are deposited the next business day after the transaction. Do exhaustive research before picking a credit card processor for the best rates. Since we do most of our business remotely, we opted for the mail order rate and the online credit card processing, allowing us to take cards anywhere in the world without a terminal. (Processing is done through MyVirtualMerchant.com)
WordPress Blogging Software
We LOVE WordPress and will be switching all of our web properties over to this great technology. The extensions, easy to use software and powerful features make it a no-brainer for us. I’ll be blogging about our setup in a future post, but if you are considering this for your firm we do offer hosting for WordPress at only $125/year. Contact us for more details.
New book for Access Web Database and SharePoint now available!
As Juan may have mentioned earlier, I had been working with two other MVPs, Tim Runcie and George Hepworth in producing the first of its kind, a book dedicated to using Access with SharePoint. The book can be bought online at the Advisicon store.
If you’re wanting to learn more about the capabilities and limitations of Access and SharePoint, this book is a wonderful starting point in getting the fundamentals as well discussion on what’s going on under the hood. It will help you make informed design decisions whether you build an Access web database or use traditional Access application linked to SharePoint lists.
Click the image to purchase the book.
Sale: $10 off to first ten buyers!
If you use the coupon “save10bucks” and you’re one of first ten buyers, you can save $10 off the book’s price!
If you do buy the book, please let the authors know how it has helped!
Connect with us:
Access Help and Tips