Replies

@Wendell – We also avoided the .ADP approach, for the application I mentioned. We used good ‘ole Access 2003 for the FE .mdb application. All forms are unbound forms, and we use ADO code to retrieve recordsets and to write data back to the SQL Server. It was a major re-write of the data access methods, versus the previous linked tables to a .mdb BE file. But, it seems to be very fast even from painfully slow connections. The SQL Server is located in Seattle. I have personally tested adding/editing data from three remote locations (all at airports): Yuma, AZ, Nagoya, Japan, and Tokyo, Japan.

The reason we moved this database to SharePoint was that we had individuals who use Macs and cannot connect to our server. They can connect to SharePoint.

Your Mac folks would have to be using a built in Windows emulation, in order to even open up a .mdb/.accdb based application. I have very little–and I truly mean very little–experience using Macs. However, I do know that one application I have provided significant help for, in the past, is run in the type of environment that you describe. This is for a non-profit agency that deals with pairing animals that need homes with people. Their office originally used Windows-based desktop PCs, but when these older machines were refreshed, they brought in Macs instead. Their network support person was able to properly configure the emulated Windows within the Macs to use the network file share, so this should be possible in your case.

Earlier, you wrote:

Just this morning, I replaced the data in the tables of a known working version of this database and that worked.

Now, I’m left wondering if your current setup, with McIntosh machines accessing this file from SharePoint, might (?) somehow be contributing to index corruption in the tables. This is just a WAG on my part…

If you have SharePoint 2010, and Access 2010 for the development machine, you might have a valid business case with a mixed environment of Windows-based PCs and McIntosh PCs to go ahead and try the browser-based application that Access 2010 is capable of creating. However, you cannot use any VBA code in the portion of the application that will be browser based; you will need to accomplish everything using the macro editor in Access 2010. This is admittedly a lot more powerful than the classic macros you are used to using with Access 2003. It is also out of my area of expertise, so I cannot advise much further than to say that it should be possible.

Hi Ceasar,

How would the final result appear if the session start & end dates spanned two months, for example 07/30/2012 to 08/03/2012?

If you can *always* guarantee that both the session start and end dates will be in the same month, then you could create a fairly simple expression to return the desired result. However, I question if one could really rely on such a guarantee…

If spanning > 1 month is a possibility, then you will likely need to write a custom VBA procedure. A bit more difficult, but certainly do-able.

More information needed.

I, personally, would not use SharePoint in this manner for an Access application. I might be tempted to implement Microsoft’s idea of a web-based Access application, where the data is stored directly in SharePoint, in lists, and the presentation layer is browser based. But it would have to be a relatively simple application for me to even be tempted to go down this road.

If all of your users are on a Local Area Network (LAN), there should be no reason to use SharePoint at all. If you need Wide Area Network (WAN) access, then I would go with a redesign that uses SQL Server as the back-end database. We have just such a database at my place of work (Access FE with SQL Server BE, with WAN-based users). It works great, even on fairly slow connections. However, we had to do a total re-architecture of the data access methods, when we converted from a .mdb BE to SQL Server. There are no linked ODBC tables in the re-designed application.

The only macros you should ever need would be an Autoexec macro, and perhaps an Autokeys macro. All other tasks could very likely be done a lot more efficiently with VBA code, and proper error trapping. It was not possible to implement error trapping in macros, in Access 2003. You can do so with Access 2007 and higher, but I wonder if anyone took the time to add this, when the conversions took place.

Hi DJ,
SharePoint is not being used when the database is opened, correct? In other words, you are not linking to any SharePoint lists?

Do you really need to open your form with all records in the recordset? This is essentially what this macro does. If you only need to open the form to a new record, then I would try eliminating the macro from the On Open event of the form, as shown on the Event tab for Form properties. Instead, select the Data tab. Then set the Data Entry property from No to Yes. This will allow the form to open to a new record, but the recordset will not include previously saved records.

Hi Andrew,

Unfortunately the setting on the PC is not for exclusive.

Okay, for each affected user, try the following experiment as THAT user:Use Windows Explorer to open the shared folder. Then:

[*]Create a new text file in the shared folder. Add a single word or string of characters.
[*]Save and close the text file. Then re-open it. Try editing the contents.
[*]Close the file and try deleting it.

You should have success with each operation, when tested as that logged in user.

Also, it sounds like you may not (?) be running a split database or, if it is split, you are still sharing a common Front-End (FE) application file. In a properly split application, each user will have a separate copy of the Front-End (FE) application file installed on their local hard drive. This way, it is impossible for any two users to ever be sharing the same FE application file.

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm
[/COLOR][/SIZE]
Also, see this “Gem Tip”:
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB
Note: For some strange reason, I had to try two times, before the above link worked. The first time I tried it, it opened up some kind of web-ring page. I’ve seen this before with these “access.qbuilt.com” links…

Oh, geez, no problem! You simply addressed me by my login username–I’m sure you could have called me much worse. :rolleyes:

The solution I just posted is similar, but it avoids the use of the Domain Aggregrate function DLookup in the query. Using Domain Aggregrate functions in queries can be performance killers. The subquery will execute much faster.

Hi Alison,

I don’t know that filtering would be so easy, but will you settle for changing the recordsource of your main form? All you need to do is add a subquery that references the table that contains the subform records to the recordsource for your main form. Here is an example that you can follow with the sample Northwind (2003 version) database:

1.) Open the Categories form in normal view. There are (8) category records. Add one or more category records, without adding any associated products.
2.) Open the Categories form in design view. Note the recordsource for this form: Categories table.
3.) Click the build button, to create a new query as the form’s recordsource. Add all fields from the Categories table.
4.) Enter the SQL for a subquery into the Criteria for the CategoryID field: In (SELECT CategoryID FROM Products)

The complete SQL statement for the Categories form is now this:
[Code]
SELECT CategoryID, CategoryName, Description, Picture
FROM Categories
WHERE CategoryID IN (
SELECT CategoryID
FROM Products)
[/Code]

Now open your form. Note that you will still only see the 8 records, but you shouldn’t see the additional record(s) that you added in step 1, above.

Hi SJ –

The combo box named “Department”, on form “frmGetDepartment”, has the following Row Source:

SELECT [tblDepartments].[DepartmentID], [tblDepartments].[Department] FROM tblDepartments;

So, the numeric DepartmentID is the first column selected, and the text value from Department is the second column selected. The Bound Column for this combo box shows column 1. Thus, the combo box feeds a numeric value to your query criteria, which is based on the text field.

Fixes include one of the following:
1.) Change combo box bound column from 1 to 2 OR (not and)
2.) Remove the first field from the row source of the combo box —> SELECT Department FROM tblDepartments;
This way, the text column Department will correspond to the bound column 1 OR (not and)
3.) Change the query criteria, so that instead of being on the text field Department, it is on your DepartmentID field.

Fix # 1 is the quickest, but not necessarily the most intuitive, as far as long-term maintenance of your application goes. Six months from now, will you be able to quickly spot that the bound column is 2 instead of 1?

Hi Jerry,

Some versions of Windows 7 include a virtualized copy of Windows XP that you can run. If your version of Windows 7 includes this option, this would allow you to keep running your existing Winworks program, giving you more time to work on a replacement. I know that Windows 7 Ultimate offers this capability. Another alternative would be to install virtualization software, such as Microsoft’s free Virtual PC. However, that option requires that you have separate licenses for any software you install, including the operating system. But, if you happen to have an older (legal) copy of Windows 98, Windows ME, Windows 2000, or Windows XP lying around, then all you need to do is download and install Virtual PC, install your operating system, install Winworks, and copy your database file to this new virtual machine.

Notes:

[*]There are some other free alternative virtual machine software packages available as well. Don’t think that you must be limited to Microsoft Virtual PC. I use VMWare Workstation myself, but this is not free virtualization software. It is very good, but it comes with a cost (~$200 USD).
[*]You will want a somewhat modern PC to run a virtual machine effectively. This includes lots of RAM memory. I recommend a minimum of 2 GB for a 32-bit machine, but 3 GB is even better. If you have a 64-bit machine, with a 64-bit version of Windows 7, then you will want 8 GB RAM minimum.

Hi Bill,

I hope your trip to the hospital goes great, and that it is nothing too serious.

I have made some improvements to this sample:

[*]Added the ORDER BY clause, as discussed earlier. Now, the Categories should always display in alphabetical order.
[*]Added an Autoexec macro that came from John Viescas and Jeff Conrad’s Access 2007 book, “Microsoft® Office Access(TM) 2007 Inside Out“. The purpose of this macro is to detect if the folder is trusted. If it is not trusted, display a message to the user.
[*]Determined that you have (6) duplicate Category entries. This was evident, as soon as I added the ORDER BY clause. I created a new Grouped query, named “z_qryDuplicateCategoryEntries”, to reveal these duplicate records. So, after removing the duplicates, you should have (50) Category records. I left the duplicates for now, so that you can see how this new query works.

Note: I name informational queries, that are not used as rowsources or recordsources for other objects, with the leading “z_” prefix. This way, they sort to the bottom of the list, and I know for certain that I can change the query without affecting any other functionality.
[*]I forgot to mention this last night, but I added a Referential Integrity (RI) constraint to your existing relationship, between the Category and CategoryType tables. A relationship without RI is little more than an exercise in drawing lines. I also set the fkCategoryType field, in the Category field, as Required. This will prevent the possibility of entering a Category, but having the corresponding fkCategoryType value remain null (unknown).
[*]In order to prevent the problem outlined in the third bullet, above, with duplicate entries, you can add an index to the Category field, as follows. However, you will need to remove the (6) duplicate records first:
.
Indexed: Yes (No Duplicates)
.
If you need the same Category available for more than one CategoryType, then use a combined field index such that the combination of Category + fkCategoryType cannot be duplicated.

Please see new attached sample.

Happy Learning!

Bill,
I don’t know why you think this is so difficult. The code to determine which to make visible is this:

cboRefund.Visible = (cboType=”Refund”)
cboIncome.Visible = (cboType=”Income”)
cboExpense.Visible = (cboType=”Expense”)

Mark – Why would you recommend having three controls, when one synchronized combo box should do the job just fine? With separate combo boxes, a person would need to make form design changes every time they needed to add a new Categorytype. For example, if your customer needed a new Categorytype of “Investments”, any such design should allow the user to do this by adding records to tables only. I do not see that this would be possible by having dedicated combo boxes, with code to control visibility. The fact that your solution involves any code means that it would only work in a trusted location, similar to the synchronized combo boxes sample I provided…

Hi Bill,

It sounds to me as if you are running your database in an untrusted folder, thus, the AfterUpdate event procedure I mentioned is not being run. Please refer to this article for more information:

Dealing with the Trust Center (Access 2007 only)
http://www.accessmvp.com/TWickerath/articles/trust.htm

As an experiment, try removing the RowSource for the lower combo box entirely (it is currently a SQL statement: “SELECT pkCategoryID, Category FROM Category;”. I suspect the symptom you will now observe, with the VBA code disabled, is that no matter which CategoryType you select (Expenses, Income or Refunds), the lower, dependent, combo box has no records.

Your Category table has 56 records total; (49) are classified as Expenses, (5) are classified as Income, and (2) are classified as Refunds. Here is what I see when I run the revised sample I provided (in a trusted folder location, of course):

Expenses (49 records):
31162-Expenses

Income (5 records):
31163-Income

Refunds (2 records):
31164-Refunds

49 + 5 + 2 = 56 records, as found in your Category table. Later tonight, I can post an amended sample, which includes an Autoexec macro that helps alert the user if they are running the database in an untrusted location. I’ll also add an ORDER BY statement, to the SQL in the AfterUpdate event procedure, as I see this morning that I missed that little detail (Category records are currently not shown in alphabetical order all the time).

Hi Bill,

The first two changes I made have nothing to do with the combo boxes, but I consider important nonetheless:
1.) Disabled Name Autocorrect.
2.) Disabled the option that allows one to make design changes to a table in Datasheet view.

Name Autocorrect (AKA Name Autocorrupt) has always been buggy. I recommend always disabling this “feature” in all Access databases. I guess I’m just a purist, but it violates my senses to have the Add a column feature available in datasheet view. I have nicknamed this feature the denormalization wizard, since it encourages new users to add fields to tables, without necessarily thinking through the database design.

In the Category table, you had a field named fkCategoryType, but this field was a text data type, and had the same values as the lookup table, CategoryTypes. The text data would only be appropriate if you were using text-based primary and foreign keys. I deleted this field, and renamed the empty field with this name. I then populated this field with the appropriate numeric values. Note: I’ve already forgotten the original name of the 4th empty field, and at this point, I would have to restart a virtual machine, with Access 2007 installed, just to quote the original fieldname correctly.

Your two combo boxes were named “Text0” and “Combo2”. I renamed these to “cboCategoryType” and “cboCategory”, respectively. I also reset the bound column of the Text0/cboCategoryType combo box from 2 to 1. This way, when one selects a value in this top combo box, the bound column will be the first column in the rowsource, which is the numeric primary key.

Make sure to enable code in your database now, since the first combo box, cboCategoryType, now has an AfterUpdate event procedure. This code will not run unless the database is in a trusted folder (Thank You, Microsoft! [NOT]).

Hope this helps,