If you are like me, you love source control. I recently discovered you can use source control in Access via a plugin. However if you want to push it out to a client, how do you remove source control? What you do is a compact and repair of the database. It will ask you if you wish to remove source control from the new copy of the database, click yes and your should be good to go.
Microsoft Access
MS Access Error: Too few parameters. Expected x
If you are getting this error it means you are probably working. Here is the deal: this error happens when you try to access an Access Database through ODBC or OLEDB. Chances are you are trying to use Access’s standard of using double quotes (“), replace them with single quotes(‘) and you should be off and running.
If that still didn’t fix your problem the error is probably quite literal in that you forgot to add a parameter to your query or that you are missing some search criteria.
Let me know if this doesn’t work for you and I can help you get up and running.
MS Access Upsizing to SQL Server Issues & Troubleshooting
Upsizing Access Databases to SQL Server?
If you are like me you have had one or two very small Access databases that have grown and need SQL Server’s power. Naturally you run the upsizing wizard and move over all your tables. This allows you to keep the Access fron-end and have a rip-roaring SQL Server in the back-end. Here are a couple things to look out for:
Careful with the Switchboard
Migrating the switchboard table over to SQL Server is a good idea if you want have many people using the Access database front-end. If you can figure out the switchboard table, you can modify your switchboard using this table.
If you migrate the switchboard table to SQL Server, you can no longer use the Switchboard Manager
To get around this issue, you have have to modify the table directly.
Timestamps: Problems Updating Data
Be sure that you include a timestamp on your tables, Access needs these if it is going to make some DML changes to your data. I believe although couldn’t find verification that it uses timestamps on the tables as a locking mechanism and reduces contention.
New Problem, Indexes
Primary keys do not come over appropriately. They come over as unique non-clustered indexes, not clustered. This means your data can be stored out of order on disk, which is very inefficient for larger tables. Delete these indexes and create primary keys for them!
Lastly, any changes made to indexes (creating, altering, dropping) will need the Access front end to be updated. Be sure to refresh your linked tables in the linked table manager.
Enjoy!
Microsoft Access Reports Printer Settings
Problem
Here is something interesting I have run across. I sometimes write reports in an Access database. As soon as I transfer the report over to the client’s computer, the computer appears to lose its default printer settings.
Background
As it turns out, Access remembers your printer settings from the last time you ran the job. I have also found that if you have a different printer installed than where the report is going to be used, it will lose the settings. So if you need a certain behavior to occur like duplex printing do the following:
Solution
- Go to the client’s computer which is going to run the report.
- Open the report in design view and make any changes you need.
- Ctrl-P to bring up the print dialog, make any changes you need for the printers configuration (you are re-establishing the desired printing setting here.
- Print one or two pages.
- Save the report.
This will hopefully save you some time in you MS Access reporting.
Compound Primary Key SQL Trickery Using Exists Keyword!
I recently came across an interesting problem. What I was trying to do was to figure out which id’s were new to the quarter 201001. Take a look at the following queries.
select id
from table as a
group by id, quarter_index
having count(*) = 1 and quarter_index = 201001
select id
from table as a
group by id
having count(*) = 1 and exists (
select quarter_index
from table as b
where b.id = a.id and b.quarter_index = 201001)
Table looks like this:
| ID | Quarter |
| 1 | 201001 |
| 1 | 200901 |
| 3 | 200901 |
| 4 | 201001 |
The first query produces ID’s 1 and 4. The second produces the ID 4. The difference between the two queries are as follows: query 1 is including both the quarter_index and the id in the group by statement thereby including row 1 from the table; query 2 is much different, it is only using id in the group by and is using the exists word in SQL. In query 2, the first part of the query is returning ID’s that only “exist” in one quarter. The second part of the query is saying “of this group of ID’s, give me only the ones that exist in quarter 201001”.
This is something that is very interesting and efficient as well. This is the first time I have used the exists keyword in the having portion of a SQL statement. I think it is rather cool and hope this helps someone.
Things I have learned today: Word and Access Tricks!
I have come across some things today that I need to share, because I have not found them many places! My main purpose on here is to be helpful to others as well as maintain a digital image of my brain; simply because, like many people in IT, I have to address many different things in any given day.
So on with the tricks!
Access:
Turning Off Stubborn Warning Messages
Today I was helping a colleague with a macro they were creating. The person turned the warnings off and were still getting the warning message about appending records. Here is how you get around warning messages that will not turn of the default way. Use the SendKeys Action immediately BEFORE the append query (or whichever action is generating the warning) you need to hide. Be sure to set it’s “Wait” attribute to “Off”. The enter key is represented as a tilde “~”, which will automatically post on the message box. If you need to tab to the the next button, you can use {TAB}.
Open Access Database on a Network
Another thing I learned today is that the Window XP will not allow you to open an Access database file when it is on a network (non-Active Directory anyways). Also the file must not be open by anyone else.
Word:
Dynamically Loading Images Into Mail Merge
If you are doing a mail merge and need an image to change on each record here are some tips. Paths have to include an extra backslash so \’s are now \\. The format is {INCLUDEPICTURE {MERGEFIELD ImagePathNameHere}}. Once you generate the output to a different file, select all (Ctrl + A) and then press F9 to have all the pictures load. If you this incorrectly one of two things happen: you see the same image for all the records; if you see this, chances are you did not select all the records and refresh them. If all the images appear like broken links, you did not include the extra backslashes in the file path.
If you have any questions about this or anything else, I will do my best to answer you. Just leave me a comment!
Refactoring Self 
