I recently needed to export all the tables from a secured mdb into a new accdb I was creating. Now, you can export each table, 1 by 1, manually, but the becomes tiresome and tedious quite quickly. Instead, I wrote the following simple function to do the work for me.
All it does is loop through the table definitions to identify each table in the database, and as it does, I export that table.
Have you tried to publish as a web app an Excel Workbook and received an error regarding Data Validation (as shown in the image below) stating “The workbook cannot be opened because it contains the following features that are not supported by Excel in the browser. Data Validation. …”.
[begin rant]Isn’t it great that MS has made its own feature incompatible with its own software! Brilliant (again)! What is even better is they do not provide a tools to convert, render a file compatible.[/end rant]
Now the only source of help I could locate was an explanation that you could use the find utility to locate cells with data validation and the delete them one by one…. For a simple workbook, maybe and even then. But when you are working with 10s, 100s of worksheets with 100s, 1000s or rows/column it simply become unfeasible to even consider doing this manually. As such, I put together the following routine to clean out any existing Data Validation from a workbook. This is a brute force method, but it does work.
'---------------------------------------------------------------------------------------
' Procedure : ClearAllDataValidation
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Remove all data validation from a workbook to make it compatible with
' SharePoint
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2013-Jan-22 Initial Release
' 2 2023-10-11 Updated Header
' Added missing variable declarations
' Updated Error Handler
'---------------------------------------------------------------------------------------
Function ClearAllDataValidation()
On Error GoTo Error_Handler
Dim ws As Worksheet
Dim Cell As Range
Dim wsVisible As Boolean
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In Worksheets
Debug.Print "Processing worksheet :: " & ws.Name
wsVisible = ws.Visible 'Original visibility setting
ws.Visible = xlSheetVisible 'Make the worksheet visible
ws.Activate
For Each Cell In ActiveSheet.UsedRange.Cells
On Error Resume Next
If Cell.SpecialCells(xlCellTypeSameValidation).Cells.Count < 1 Then
'No validation
Else
Cell.Validation.Delete
End If
On Error GoTo 0
Next
ws.Visible = wsVisible 'set it back as it was originally
Next ws
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Source: d" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
I would like to refine it further, but well see. Since this is the type of thing you only need to do once, optimizing it further isn’t a priority right now. Time permitting down the road. Off to put out the next fire for now.
Also, if you are looking for a listing of what is and isn’t compatible between regular MS Excel workbooks and those open using a web-based browser, see: https://web.archive.org/web/20110222121738/http://office.microsoft.com/en-us/sharepoint-server-help/differences-between-using-a-workbook-in-the-browser-and-in-excel-HA010369179.aspx. There you’ll see a listing of what is compatible, what isn’t and what might behave differently. For instance, workbooks with VBA will have the VBA non-functional and the file itself will not be editable!
So at the end of the day, using Excel files through a web-browser will only work for the most basic workbook! Be forewarned.
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.
That said, I must say that once things are setup properly, CITRIX can be very reliable and a very useful tool.
I have been a Bell customer for well over 20 years now and for the first 10 years I had no problems, minor glitched here and there but nothing major. Since moving to my new residence, almost 10 years now, I have sadly had a number of problems relating to Phone and Internet.
Here are a few problems that I experienced with Bell in the past few years:
Today, yet again, I find myself having problems with e-mail. I can’t access them through my standard e-mail client (MS Outlook), nor can I log into their webmail (as illustrated in the image below).
What I love even more is reading the ‘Customer Commitment’ (see the image below)
So they state that they will strive to be accessible through the medium of my choice, yet you can’t send them an e-mail, nor chat with them, nor send them a text message. So the medium of my choice is to sit on the phone, deal with tech support that can’t normally help whatsoever, and get transferred around for a minimum of half an hour each call (and that is a minimum based on my past experiences and some of clients). You go on their support page, specifically regarding Internet and see that the chat button is not enabled. Thus, the only means at a customers disposal is phone support.
When things work with Bell’s service all is good, but sadly when things go wrong, their service is some of the worse I have had the misfortune of experiencing. Support agents that you cannot understand, that do not have the skillset to resolve even the simplest of problems, etc… Then the company comes out with Customer Commitment statements like the one above but give a contact page which simply does not reflect their supposed commitment to their client, a farce is what it is. Sadly though, the alternatives are not better. Based on many of my business clients experiences, comments from friends and family, all then major players are the same.
It is a very sad state that we find ourselves in. These companies, in my case Bell, want to charge you 50$/month for this, 40$/month for that, and don’t be late with your payments, but the minute you need them to actually service the service you are paying for, then things get dicey! And don’t expect the ever get an apology, a refund or anything else even if the problem was entirely their fault.
What is even worse with Bell specifically is the fact that one used to be able to contact their Customer Support by e-mail and they did away with it. So Bell has in fact reduced their support to their clients in the past years.
If only customer service meant something to such companies.
I have been asked a few time over the years
what should a developer be asking a client when starting a project?
Sadly, if you are asking this question, you need to rethink your level of experience to tackle the project you are looking to take on as any experienced database developer has a good idea of what questions to ask.
Here are some starting questions. Obviously, however, the discussion with potential clients usually leads me to ask many more questions, but everything depends on the context of the conversation, the project,…
General IT Information:
Database Current Specifications:
Work Mandate Specifications:
General Questions:
Although the above centers around MS Access, it can be generalized quite easily for any database project.
As of Access 2007, Microsoft stopped promoting User-Level Security (ULS), although it is still supported per sey. As such, most developers now require a new means to control access to their databases. Now ULS had it pros and cons. Although it was effective, sometimes it was simply overkill for simply controlling longins, and should you loose the mdw, or it becames corrupt, well lets just say you were in for a real treat and hence the critical importance of making regular backus.
Anyways, back to the subject at hand, what is one to do now to control access to a database using Access 2007 or later? Well, there is no point on me to try and reinvent the wheel on this. That said, this is a very common forum question and I usually refer people to the same list of links to learn from. So I thought it would make sense to post them on my site, in case someone Googled the subject. Here they are:
Simple User Login
Login/password and access level system
Security and User Tracking DB
Db Login
Creating Login Security using Access VBA
Login Security using Access VBA
Creating a user login form
Log-On form…
They are all different in complexity. So check them out, one by one, and find the security model to suit your needs and then you can simply implement it and tweak as needed.
I hope this helps.
Over the years, I have answered numerous questions regarding how to export a records, or records to either MS Excel or MS Word. I already have 2 posts on the subject:
but thought a concrete example would help illustrate things even further.
Here are a few screenshots of the sample.
Now in this sample I am covering Excel and Word automation, not built-in commands, such as:
These are well documented, and plenty of examples can be found online. Instead, I concentrate on demonstrating a few possible ways to export using late binding and word and excel automation, permitting much more control on the final product (font, colors, layout, page orientation and so much more) and no need for any external reference libraries.
Note: for this sample to work, all the supporting files (excel and word) must be in the same folder as the database itself (although this very easy to change in the VBA code provided).
With the replacement of the command bars with the ribbon in Access 2007, I have started to always use a standardized ribbon for all my reports. Now, I typically used 2 template reports which I have configured my typical layout, colors, header & footer formatting, etc. and of course defined the Ribbon Name. But like everyone else, sometimes, I start a new report from scratch, modify an existing one and forget to add in the Ribbon Name property and thus the user would not have displayed the ribbon allowing the to print, export or merely close the report; forcing them to shutdown the database just to be able to exit/close the report. So I needed a simple means to ensure all my reports included this ribbon.
I already have a DeployMe function that I run everytime prior to deploying a new version of any database I create to my client’s which sets certain properties, ensures things like auto compact is disabled, auto correct is disables, and much more, so I simply wanted to create a function the I could add to this routine. Below is what I came up with. A simple loop that open each report one by one, sets the RibbonName property and then closes and saves the change. Short, sweet and simple. Best, it work.
'--------------------------------------------------------------------------------------- ' Procedure : SetReportRibbon ' Author : Daniel Pineault, CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Loop through all the reports in the current database and assign the ' specified ribbon as the report's ribbon ' 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). ' ' Usage: ' ~~~~~~ ' Call SetReportRibbon ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 2012-Dec-07 Initial Release '--------------------------------------------------------------------------------------- Function SetReportRibbon() On Error GoTo Error_Handler 'Ensure that all the reports are set to use the ReportUtilities ribbon Dim DbO As AccessObject Dim DbP As Object Dim Rpts As String Set DbP = Application.CurrentProject For Each DbO In DbP.AllReports DoCmd.OpenReport DbO.Name, acViewDesign If Reports(DbO.Name).Report.RibbonName = "" Then Reports(DbO.Name).Report.RibbonName = "ReportUtilities" End If DoCmd.Close acReport, DbO.Name, acSaveYes Next DbO Error_Handler_Exit: On Error Resume Next Set DbP = Nothing Exit Function Error_Handler: MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: SetReportRibbon" & vbCrLf & _ "Error Description: " & Err.Description, _ vbCritical, "An Error has Occurred!" Resume Error_Handler_Exit End Function
I always find it amazing that even with the internet, it can sometimes be very hard to sift through all the available materials 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 whose techniques can be used with any RDMS. So if you are starting off, or merely brushing up, here are a few good links to consider:
For starters, some of the best websites are those created by the various Microsoft Access MVPs. I have compiled a separate list of their websites in the following post: https://www.devhut.net/mvp-sites/.
Some of the most valuable websites (especially regarding Access of course) are those of many of my fellow MVPs (and a few other exceptional individuals). So I thought it intelligent to try and compile them here. If you are looking for some of the very best online resources, these are the sites to start with! Packed with invaluable information, explanations, sample databases to quickly learn from, these sites are the ‘crème de la crème’!!!