SQL Server Reporting Services the easy way

[pkstormy]

SQL Server Reporting Services (SSRS) is one way users can design queries/reports against SQL Server data. But there are a lot of things that must be considered when giving users access to SQL Server Management Studio, replication, permissions and so on. Teaching users SSMS and SSRS is also challenging along with the requirements to know SQL (versus graphically designing a nested query) and code can get fairly complicated when designing nested or other types of queries. MSAccess is a great utility for designing queries and linking data to other MS Office products such as Excel and Word.

The attached is a simple MSAccess application I designed 20 years ago which gives users access to all their latest SQL Server data in a safe way. I've used it very successfully at dozens of places and thought I'd fancy it up and post it. This is the way it works:

1. There are about 5 buttons on 1 form called Main Menu. This form is set to open automatically but has a close button on it to allow users to quickly get behind the scenes to the tables/queries/reports. I've found almost everyone picks up learning MSAccess fairly easy.

The "Setup SQL Server Parameters" button is for the DBA and allows you to enter SQL Server connection parameters and which database and which tables you want this MSAccess file to import (you only need to do this once). The code is setup to only grab data tables from 1 SQL Server database but you can make a copy of this MSAccess file and use it for a 2nd SQL Server database. The connection information is stored in a hidden table so users won't typically see it unless they turn on the option to see hidden tables for any reason (they should not need to do this.)

When the user opens the MSAccess file, all they need to do is click the 'Refresh SQL Server Data Tables" button (top button on the Main Menu). Clicking this button will import the latest data tables from SQL Server (the ones designated in the setup section.) The import/refresh process takes anywhere from 10 seconds to 2 minutes depending upon the size of the data and number of tables. I found the average refresh time is about 30 seconds if unneeded tables are eliminated. Users have told me they would much rather wait 2 minutes to get a freshly cloned dataset and design queries the way they want to versus learning SSRS and dealing with individual table permissions, SQL, etc.. Running this process does bloat the MSAccess file each time so there will be the occasional need to Compact and Repair the MSAccess file (this is the only drawback I've found using this technique). MSAccess has a 2 gig file limitation (which holds a lot of data) and I added some code to the Main Menu form that will show a big red message to Compact and Repair the Database when the file size bloats to over 1.8 gig. Running the Compact/Repair will shrink the MSAccess file down in size. If your data exceeds the 2 gig file size, you can configure the import code to get data from a stored procedure which limits the dataset.

The refresh process basically sets up a 'temporary' connection to SQL Server and imports the specified data tables. After that there is no connection to SQL Server so any updates the user makes to the data tables will NOT be uploaded back to the SQL Server data. Testing has shown the refresh process does not impact other processes running simultaneously on SQL Server but if it does, simply designate a specific time for users to refresh the data (users have told me that they like the ability to update data the way they want to and that they can then click refresh data to restore the latest dataset.)

I also designed a simple query designer form if new users struggle with MSAccess queries. It allows the user to easily create basic queries with criteria based on 1 table.

Users also liked the ability to mailmerge the MSAccess tables to Word Documents (something tough to do with SQL Server tables) or design their own reports. Almost everyone preferred designing queries/reports in MSAccess versus SQL Server due to the ease of use.

As a DBA, I love this application because the user get's an exact copy of live data whenever they want it (they only have to push 1 button on the Main form) and I don't have to setup replication or setup SSMS for users or manage table level permissions or worry that a user is going to update any SQL Server data. For HIPAA compliance, I created an additional SQL Server table (not attached) to hold LoginIDs allowed to open the attached MSAccess file. Typically though if you don't need high security, you can just copy this MSAccess file to the user's folder giving it the same security as the other documents in their folder. Having multiple users in the same MSAccess file is not recommended! (1 reason is the 3 user file limitation, the 2nd reason is to limit the number of tables needed and confusion amongst queries/reports designed.)

This application has been a real time-saver for me and it protects the live SQL Server data without the need to configure a whole bunch of settings on SQL Server. If users accidently update/delete data in any of the MSaccess tables, all they need to do is click the 'Refresh SQL Server Data' button on the Main form to get the latest data again. If a field is added to the SQL Server table, the user only needs to click the refresh button to then see it.

The biggest obstacle I've faced with this application is the stigma against MSAccess. MSAccess isn't a great transactional database server like SQL Server but it is a great conduit product for SQL Server data to other Office applications. The Table Relationship designer is 10 times better in MSAccess versus using SQL Server diagrams. I've also seen some great excel reports designed against the data tables imported into this MSAccess file. Most users find creating queries in MSAccess much easier.

ODBC and other help is implemented into the MSAccess forms and included a link to a simple vb script I designed years ago which allows you to have thousands of users running the same MSAccess code simultaneously in a client-server environment, overcoming the 3 simultaneous users limitation.

[isladogs]

I have downloaded but not yet looked at your utility.

What is this 3 user limitation that you are describing?
I've never heard of any such limit
There is a 255 user limit in terms of available connections.
See https://support.office.com/en-us/art...8-98c1025bb47c
I've regularly had over 200 current users in my own split databases using both Access and SQL Server BE files.
SQL Server is of course much more stable so that is the default structure I use.
By comparison, an Access BE will struggle with so many users and quite possibly crash.

Obviously each user has their own copy of the FE in my multiuser environments..

Multiple users should NEVER be using the same FE file.
To say it's NOT RECOMMENDED isn't strong enough.
Doing this WILL lead to corruption whether it's 2, 3 or 200+ users.

Coming back to your utility, I'm still not clear what it's purpose is.
Perhaps I'm missing something obvious?

When I distribute an Access FE for use with a SQL Server BE, I use DSN-less connection strings which only need to be done once.
For me, this is much more efficient than having to setup ODBC connections on each workstation
It also means that end users have no direct access to the server files.

There are times when I want users to be able to practice using the application e.g. in a training session for new staff.
In such cases, I provide copies of the FE that have been relinked to a dummy version of the data file.

[pkstormy]

It sounds like you got a great handle on using MSAccess and the pitfalls. I was referring to having more than 3 users in the same office file on a Windows share drive (whether it be MSAccess, Word or Excel.) With MSAccess, the *.lccde file appears when any user opens an MSAccess *.accde file (to track all users in the *.accde file) but it still gets corrupted easily when one of the users loses network connection. This then requires all users in that same *.accde file to close out of the *.accde file so it can be compacted/repaired (I'm guessing this is the same reason you probably don't have multiple users in the same front-end MSAccess file.)

I link the SQL Server tables into the MSAccess front-end with a system ODBC in the *.accdb file and then compile that into an *.accde to copy to the share drive. I've used DSN-Less connections in the past and your correct in that it is useful for certain environments. For the hospital environment with medical patients, using a system ODBC worked best with vpn. Using linked tables, nested queries and a whole mess of nightmarish logic to randomize patients to different treatment conditions/medications was ideal versus writing stored procedures. The vb script allows me to manage dozens of MSAccess applications simultaneously with daily changes by the users to one project or another.

The vb script can be used for any kind of file. I use it to clone the source *.accde (ie. compiled MSAccess file on the share drive) and make a copy of it (adding the user's loginID to the file name). Using the script, you can tell it to take any file on a share drive (such as S:\Databases\MyMainDBFrontEnd.accde), copy it to a specific folder adding the LoginID to the file name and then open the newly cloned file. For some projects, I would have the vb script clone the MSAccess front-end *.accde file from the network share drive to the user's C: drive into a hidden folder. The last line of the the vb script then would open the file that was just copied (ie. C:\Databases\MyMainDBFrontEndPaulK.accde.)

I have the source *.accdb file (MyMainDBFrontEnd.accdb) on my development drive. When I make code changes, I simply compile and copy to the new *.accde file to the share drive. All I have to do after that is send out an email to all the users telling them to close/re-open the database (I have a main MSAccess menu system which has buttons that launch the specific vb scripts.)

If I clone the files to the share drive (versus the user's C: drive), I can then also easily see when the user last opened the MSAccess file since there will be a timestamp on the MSAccess file with their LoginID in the name. This helped me several times in making sure the user was opening the database the correct way (ie. through the menu system I designed). I would occasionally get some users who wanted to find the file themselves instead of opening it through the menu system I designed. If I ever saw a file such as MyMainDBFrontEnd*.lccde file in the source folder on the share drive, I then knew someone bypassed the menu system and opened the MSAccess *.accde file directly (I also had code in the MSAccess front-end which exited the user out of MSAccess if it saw an *.laccde file with the source file name.)

As a DBA, I also use MSAccess to initially create the tables, relationships, keys, indexes, etc.. I worked with Microsoft back in 1992 on the relational diagramming tool in MSAccess 2.0 and the relational diagraming in MSAccess is still 10 times easier to design in versus using SQL Server. The SQL Server Upsizing Wizard has worked like a charm since the old days and it takes me less than 1/2 the time developing a database with 100+ relational tables initially in MSAccess.

The main reason I like using the vb script though is that if a user loses network connection while in the *.accde front-end file and corrupts it, I just tell the user to open the database again from the main menu. When they open the main menu and click on the button to launch the vb script for their database. The vb script will copy over their existing corrupted .accde file and open it without problems. I never have to tell a user to wait until I compact/repair the front-end.

[isladogs]

@PKS

You also clearly have a good understanding of Access/SQL Server & network issues.
Yet you allow up to 3 users to share the same ACCDE file on a shared drive.
That's about as logical as the 3-second rule (or 5 seconds for some people) that food dropped on the floor is safe to eat if picked up within that time!

The reality is that you should NEVER have two or more users sharing the same file and Access FE files should never be run from a shared drive.
An analogy I often use is that you wouldn't allow several people to drive your car at the same time as it will crash. Similarly with Access (or Excel/Word) files.

Clearly the more users there are the sooner a problem is likely to occur. One of the main issues is indeed dropped network connections leading to corruption.
I'm not so worried about the lock file getting corrupted (its just a text file) as the FE itself or the data in the BE getting corrupted

Whether you use ODBC or DSN-less connections is partly personal preference/circumstances.
In my case, I distribute my applications via my website.
Many of my commercial apps are used in various UK schools and there may be 200-300 users at each site

When application updates are released, system admins are alerted automatically when they next run that app.
The admin downloads the new version, relinks it to their BE files (different locations for each client) then places the updated version on the network.
Where necessary the installation script will also make changes to the BE file(s) e.g. add new tables/ modifiy field sizes.

When end users open the app from a desktop shortcut, they are actually opening a small utility file which looks like a splash screen
This checks whether a newer version exists on the network & if so downloads it automatically before opening the main app.

As end users have no direct access to the network,this is an effective way of guaranteeing all users are running the latest version.
From experience, relying on staff to download something themselves means many will fail to do so for a variety of reasons.

Other people (like yourself) happily use a vb script for the purpose.
In fact, I know of several developers who have designed systems where a fresh copy of the FE is downloaded to each PC every time the application is run.
Whilst I understand the point, to me that seems like unnecessary network traffic...
....especially when 200+ staff may login at once e.g. to take attendance registers at the same times

For info the Upsizing wizard was removed from Access in version 2013.
However, the more powerful SQL Server migration assistant is still available as a better alternative (if you can get it to work!)

Finally, I agree that the Access query designer is still very good for many purposes even though it hasn't been updated for many years.
There are issues e.g. not being able to open a new query direct in SQL view, lack of colour coding to assist with understanding of long SQL strings etc.
I prefer it to the SSMS equivalent but mainly because I use it more often.

[pkstormy]

I like comparing notes on our methods.

You are correct that the upsizing wizard was taken out of MSAccess in 2013 and became SSMA which has a 32 bit and 64 bit version. You're also correct that it is very challenging getting SSMA to work especially when deciding on whether to use the 32 bit or 64 bit version. I misspoke and still tend to call it the SQL Server Upsizing Tool versus SQL Server Migration Assistant.

I think you're misunderstanding the intent of the vb script. From your description, it sounds like the vb script works similar to what a button does on your menu utility. Like you, I never, ever, EVER allow 2 or more users to be in the exact same *.accde file. When the user clicks a button on my main utility (I call it Main Menu), it launches the vb script. Instead of doing a version check though, the vb script just makes a copy of the *.accde from it's source location (in a hidden folder on the share drive) and then opens it. I also have code in the *.accde front-end which prevents the source from ever being directly opened. I only use the source *.accde file to spawn out copies for users.

I did away with trying to do any version checking each time the user opens the front-end. I do display the version number on the research project's menu (changing it with each new version) but I may have up to 400 changes (by the doctors/researchers/staff) after the initial version release and it's easier to just make a copy of the latest version. Doing any kind of version checking (and then making a copy if the versions didn't match) didn't really make sense and just added extra time to opening the front-end. It became easier and quicker to always just copy the latest front-end *.accde file (this takes 3 to 5 seconds). I can always tell what version the user last opened by looking at the file timestamp on their *.accde front-end and I can match that with copies I made in my development folder of the *.accdb file before I compiled it to an *.accde and posted it to the share drive.

I think though our methods are pretty much the same where we both keep the "source" *.accde front-end file in a safe place and users cannot open it directly. This *.accde file is only used for making clones for the users to open.

Am I correct or did I get things wrong?

I didn't understand what you meant though when you said "I'm not so worried about the lock file getting corrupted (its just a text file) as the FE itself or the data in the BE getting corrupted". Does that mean if someone's front-end get's corrupt, they then need to contact you to fix it or does your routine automatically fix their copy of the *.accde file and open it?

[isladogs]

That's all fine but some of that some rather different to your first two posts.
In particular, if you correctly NEVER EVER allow two users to run the same file, why did you state that having more than 3 users causes corruption?

I didn't understand what you meant though when you said "I'm not so worried about the lock file getting corrupted (its just a text file) as the FE itself or the data in the BE getting corrupted". Does that mean if someone's front-end get's corrupt, they then need to contact you to fix it or does your routine automatically fix their copy of the *.accde file and open it?

I've never experienced a lock file getting corrupted. As its a text file I'm not clear of any circumstances why that would happen.
It is extremely unusual for the FE files I distribute to get corrupted.
However as previously mentioned, the desktop shortcut actually opens a separate 'splash' updater app which checks for a newer version on the network.
If the local version is corrupted, the splash updater app can download a fresh copy automatically.
It also detects if there is a newer version of itself & downloads that if it exists
The user can also 'force' a fresh update on the unlikely event a problem occurs e.g. network connection lost during previous download from network

The attached PDF summarises how it works

[pkstormy]

I mention the 3 user file limitation because in places that I've consulted and worked (which have internal and external vpn users using the same share drive), I will sometimes see 1 MSAccess *.accdb file being accessed by 1/2 dozen users or more. Sometimes the *.accdb even has the main data tables in it (the FE and BE is not split). This is not using MSAccess correctly in a multi-user environment where vpn, citrix and other remote type connections are subtle to hiccups. When the first user opens an *.accdb or *.accde file, a *.laccdb or *laccde file is created (I think this is the text file you are referring to). When other user opens the same MSAccess file, the *.laccdb or *.laccde is updated. If a user suddenly loses connection while in the MSAccess file, the *.laccdb or *.laccde file can easily get corrupted and linger around causing problems for anyone else who wants to open the same MSAccess file. If this happens, it then requires all users to close the MSAccess file so it can be compacted/repaired. This seems to especially happen if more than 3 users are in the same MSAccess file. I think this is posted somewhere. If you've worked with older versions of MSaccess which used an *.mdb/mde file, these got corrupted easily when slower modems were connecting to the network.

I read the pdf file using the SDA. I've used similar methods in the past. Rather than making a connection to the source to see if it's the latest version, I basically just copy the latest version to the user's drive using the vb script rather than first checking. If I compile the *.accdb into an *.accde file, it takes 3 to 5 seconds to make a new copy of a 50 meg *.accde file on most networks. I occasionally see a user whose network connection freezes (leaving a hanging *.laccde file which would typically causes problems for that user to re-open their copy.) I just tell the user to re-open the database from the Main Menu (and let the vb script copy a new *.accde file which fixes the locked *.laccde file.) This has been a great time-saver for me and for network admins who would otherwise try to terminate the user's MSAccess process. There are times I will also want to track users who opened the MSAccess file and when (so I can see when they opened the db for testing purposes). To do so, I modify the vb script so it clones to a database folder on the share drive. I can then see when that user last opened the MSAccess application by looking at the timestamp on the MSAccess file that has the user's loginID in the name. Whether I modify the vb script to clone to the user's c: drive or the share drive, no user is ever in the same MSAccess file and they all have the latest version of the code.

I've attached a snapshot of what my menu looks like. This is the one I created while working at the department of medicine. To manage all applications easily, I just have 1 Main Menu which has buttons to run the vb scripts which open the independent databases for the users. When the user clicks on the button, it runs the vb script which automatically copies the latest code and opens the application. It doesn't do a version check so I don't need to factor that in any delays on opening the application and I don't have to worry about the user not getting the latest version if the network hiccupped during the version checking for any reason.

[pkstormy]

Ridders52,

I apologize if anything seemed contradictor in my previous statements.

When I started working with medical applications and HIPAA security, it seemed like a better approach to use an ODBC User dsn versus a DSN-Less connection. I wanted to share with you my situation and see what your thoughts are. These are the challenges I faced when writing research and medical type applications together.

Since I played the DBA and Developer role (I'm guessing you do also), I had to decide what was easiest to implement for security so I could play both roles in this environment because along with database management, the user's wanted a lot of complex stuff built into the front-end interfaces (ie. randomizing patients to treatment conditions, create customized patient schedules, daily call lists for patients, abilities to schedule patients, abilities to modify randomization pattererns, specialized checklists for patient visits, auto-emailing doctors, integrated auto-emailing and IVR system, etc..etc..). Basically collecting research data (in multiple ways) on certain medications for patients used to treat addiction.

The main thing was that each research project only lasted 2 to 5 years and there was a need to create multiple new research projects every year. They didn't want a centralized system but they wanted something built where training users between projects was minimal since staff were going to be accessing multiple projects simultaenously (i.e modular) and they didn't want a centralized patient type database like hospitals have which hold all patients in 1 database (separating patients by projects was easier for researchers).

One thing staff told me from the beginning was that they absolutely did not want to be prompted for a password every time they opened the application. I also dislike writing applications that prompt someone for a password each time they open it. I typically setup SQL Server to use Windows Authentication and SQL Server permissions are established using active directory groups to each database. I avoid using groups to manage read-write table level permissions and save this for the front-end since I was mainly dealing with field level permissions.

I then have code in my front-end application to automatically retrieve the Windows loginID. Front-end and application-use security is mainly controlled in a table (SQL Server) that I create in each database. This table has records of the LoginIDs and read-write kind of permissions that admin users can modify. It's similar to controlling groups of permissions on SQL Server where the group identifies the read-write permission levels to certain tables except that this is application specific so certain users can modify field level data in tables they would otherwise not have permissions to. It made managing permissions in a HIPAA type environment much easier for the users as well as for myself. For new users, they would first need to be placed into the active directory group to allow permissions to the (back-end) SQL Server database (my job or designated staff). The user would then need to be added to the security type table I designed in order to open the front-end (controlled by admin staff). Since all equipment is hospital laptops or desktops, I initially wanted to use an ODBC System DSN but both the network administrator and myself decided it was better to use an ODBC User DSN instead.

I'm curious on how you handle security in your situation.

Are you in a vpn, citrix or terminal server type environment?
Do you prompt users for a password at runtime or do you store info in the registry?
Do you have any kind of additional security built into your front-ends?
How do you handle field, table or group level permissions?
Do you compile the front-ends into an *.accde file?
Lastly, do you use ADO or DAO? (I'm curious about what is the most popular these days since Microsoft could never seem to eliminate one or the other. I personally like ADO.)

[isladogs]

Hi

No, I've never officially been a DBA
I was for many years a Deputy Head in a large secondary school with IT strategy & implementation a part of my role
Throughout my teaching career I also developed applications for schools mainly in Access & SQL Server.
Initially these were developed for my own use but later implemented across the whole school and in other schools
When I retired a few years ago, I decided to build on my existing skills and applications and now sell various apps for schools and business.

So although I have fairly extensive knowledge of SQL Server related to its use with Access, I personally use it far less than in the past.

All my schools apps require users to login to the Access FE with a user name & password.
That is vital for security as many are used by teachers in classrooms as well as as by admin staff in shared offices
The user details are in most cases checked against Active Directory.
However a few schools wanted to store passwords within the app (against my advice).
In such cases, I insist on these being encrypted using RC4 with the cipher also being encrypted by another method

As previously mentioned, several apps can have 200+ users at once in a school during crunch times of the day.
Windows authentication is used for SQL Server so the vast majority of end users aren't aware of its existence
.
I use DSN-less connections with all split databases (whether Access or SQL Server) because it makes administration much simpler for both my clients and myself.
In fact, I have never actually visited several of my client sites as the applications need minimal maintenance.
If I do need to be involved, this can almost always be done remotely
To make the apps almost maintenance free, I set up a system whereby any program errors were 'silently' emailed to me (with the clients' knowledge of course). Doing this allowed me to fix any program errors promptly.
It was hell when I first set that up but worth the effort in the long run
Prior to that, I wasn't always made aware of errors so of course these could persist for some time

I also created systems to record login details for each user - when / where / workstation info / Access version / application version together with the features used within the app. These details are included automatically in the automatic error emails and are of course useful for auditing purposes

I have minimal experience with ODBC System or user DSN as I don't use either

As for your other questions

Are you in a vpn, citrix or terminal server type environment?

I used terminal services for several years but have no experience of administering it. Never used Citrix

Do you prompt users for a password at runtime or do you store info in the registry?

See above. Lots of application info is stored in the registry of the administrators' PC but not for each user

Do you have any kind of additional security built into your front-ends?

Yes - they are very strongly locked down. No means of getting to Access options etc etc
No navigation pane or ribbon. Reduced or no menus. Some have the application window removed
In some cases no taskbar whilst the app is in use

Do you compile the front-ends into an *.accde file?

Usually but there are some exceptions which are sold as ACCDB allowing clients to modify for their own purposes

How do you handle field, table or group level permissions?

User permissions (stored in the app) determine which features they are allowed to use.
Where a feature is restricted, users who do not have permission do not see any sign of it - command buttons hidden etc (see screenshots below showing the same menu screen for administrators and standard users)
Very sensitive data e.g. child protection is further restricted by requiring a separate password - regularly changed

Lastly, do you use ADO or DAO? (I'm curious about what is the most popular these days since Microsoft could never seem to eliminate one or the other. I personally like ADO.)

I use DAO. In fact I've always struggled with ADO so avoid it as far as possible
I think DAO has been the recommended options since 2003 but as both are in wide use, MS can't drop either approach





Hopefully I've covered most of your questions! Happy to explain further if not....

[pkstormy]

Very nice menu system! It's really nice communicating with someone who uses MS Access as a front-end to SQL Server databases. There doesn't seem to be much demand for experienced MS Access and SQL Server skills in WI. Web design and .NET experience seems to be the latest crave in the area.
The department of medicine wanted to take 10 years of application development work in MS Access and convert it all into a web design (within a year!)
I told them it was a mistake and there would be some things they would not be able to convert into a web design. They want researchers to learn writing SQL in SSRS versus designing MS Access queries on their data. Hence my post on SSRS.
I was impressed reading how you've implemented things.
I can fully relate to the difficulties on setting up an auto-emailing system on errors. I always wanted to do something similar but I could never make it code-based. It's neat that you've successfully done that! I only got as far as having a button the users clicked on each interface to launch a customized ticket type application.

That's neat that you were a teacher. What classes did you teach?
What made you explore using MS Access and SQL Server over something like .NET?

I started with MS Access 1.0 after DBase III+ and the failure of DBase IV. When I was designing client-server type applications using MS Access for the Energy Star program back in 1995, Microsoft introduced me to SQL Server and showed me tips on developing MS Access applications. MS Access was never good as a transactional database over networks, especially using MS Access tables and older baud modems. I recall the 'Star' system in an older version of MS Access which tried to track primary keys by creating a random -/+ number in the billions. The philosophy was in order to over-come connection hiccups, 'Star' would convert your MS Access database into a system where the primary key was soo large, the chances of duplication were minimal if a hiccup should occur. It was a disaster of one of MS Access's earlier features, especially for financial type applications. When I started with SQL Server, I initially designed ADP projects but found limitations with them as well. Hence I started using MS Access with SQL Server.

Did you by chance get the chance to try the MS Access 'Star' feature prior to SQL Server days?

[pkstormy]

I wonder what the code to import the SQL Server tables would look like using a DSN-Less connection.

I use this code in the example which is based on an ODBC Dsn.
Dim cxnString As String
cxnString = "ODBC;DSN=" & ODBCName & ";UID=" & UIDLogin & ";" & UIDPW & ";LANGUAGE=us_english;DATABASE=" & SQLDBName
DoCmd.TransferDatabase acImport, "ODBC Database", cxnString, acTable, SQLTableName, SQLTableName

If possible, could you translate the above to use a DSN-Less connection? It's been a while since I've worked with DSN-Less connections. I'm going to do some research on using them again.

I'm hoping to modify the code to import SQL Server stored procedures instead of SQL Server tables to overcome big data size issues on tables that would cause the MS Access file to exceed it's 2 gig filesize limitations.

MS Access also still has file-size bloating issues when importing data.

Hence I built in a way to monitor the MS Access file size and show a red message box when it approaches limitation.

[isladogs]

HI again

As this thread seems to be a private conversation between the two of us only (but in public), I suggest we continue either by PM or email (my preference).
If you're happy with that, you can email my business address using the link in my signature line.

Cheers

[ssanfu]

and I was learning so much........

[orange]

I'm with Steve -- The dialog could be very useful to others.

[isladogs]

LOL.
Finally .... proof that there is intelligent life beyond the narrow confines of this thread
I assumed nobody else was interested .. or even reading it!