Replies

I’m not familiar with MicroPact. My guess is that they are exposing a “super view” (query), sans your foreign keys, instead of just exposing a single table out of 20 tables. Will they provide the credentials needed to connect a different client to the Oracle database, even if it is just read only?

If “yes”, you could probably set up the required pass-through query in MS Access, taking advantage of the powerful reporting tool that is part of Access. If “no”, you could still export the de-normalized “table” (query or view) results to Access and use that as a starting point. But, if you want your report all within the MicroPact application, then you are probably SOL.

Now I’m trying to actually use the data I got. . . of all things the report writer won’t use the !@#$ query :mad:. And I can only get their report tool to build rows, not joined columns!

The “report writer”? Are you talking about the report wizard in MS Access? Or are you using some other application, since you make reference to “the vendor”?

It’s certainly possible that the slash character (/) might cause a problem.

However I would note that your latest error message is about command termination. That terminator is the semicolon, which is valid so far as I know. It certainly is valid in Microsoft SQL Server. Nevertheless with this error message, the first thing I would try is removing that semicolon. It is possible that your query environment does something funky like automatically adding a semicolon, thus resulting in a double semicolon with the syntax we used. Or maybe the semicolon really is totally banned in Oracle.

The limited Oracle experience I have revealed that Oracle strictly followed ANSI SQL standards. SQL Server extended those standards quite a bit. This showed up most strongly, in the systems I used, in object name length restrictions I encountered with Oracle.

The slash character is fine. I have had some limited cases where a terminating semicolon caused an error, but I have only observed this when using Microsoft Access as a client, with Pass-Through (P/T) queries to an Oracle 11g Release 2 database.

The problem is that in Oracle you can optionally use the “AS” keyword when aliasing columns, but not tables. Change your query to this:

select t_c.id as “Case/Investigation ID”
from t_case t_c
join t_investigation t_i
on t_c.id=t_i.id;

Also, the word “inner” is optional. I removed it.

There’s really no mystery here.

If UAC is off (or if Disk Cleanup is run as administrator), the ‘Clean up system files’ elevation button is not there as you already have direct access to the system files categories.

@BruceR:
I was just about to post a similar comment, although I didn’t know that one’s UAC setting is also part of the equation.

I had tried the various suggestions, including uninstalling KB2852386 and re-installing it via the Windows Update site (Result = Successful), yet I still could not see the button labeled “Clean up system files”. I now know the reason: I have a shortcut to CleanMgr.exe saved to my Start Menu. I have always been in the habit of right-clicking this shortcut and selecting the “Run as administrator” option.

This morning I tried running it as a normal user, without using the Run as administrator option. And “wa-la!”, I now saw that elusive button! The display included only a single tab (“Disk Cleanup”), along with a listing of the files to delete. When I clicked on this button, a new scan was initiated. This time, the resulting dialog did not show the “Clean up system files” button, but it does include a second tab, “More Options”, which one never sees if run as a normal user.

Tom

I purchased my own Comcast-approved device, an Arris TM722G/CT (https://www.google.com/#newwindow=1&q=Arris+TM722G%2FCT), at a Comcast outlet located within a local Best Buy store, so that I would not have to pay their monthly rental fee for their device. This device supports the DOCCIS 3.0 standard (https://en.wikipedia.org/wiki/DOCSIS). While it is a modem only, I have a separate router that supports the newest wireless standard, 802.11ac.

The Comcast supplied devices, which include a modem + router in one device, only provide wireless service in the 2.4 GHz bandwidth. If your equipment was purchased after 2009, it most likely provides support for the much less crowded 5.0 GHz spectrum.

I had run tests just before deciding to purchase the modem and was getting about 6Mbits down.
Today after installing the new modem I’m getting 16Mbits down basically for free!

Upload speed about the same at approx: 0.5Mbits.

Things sure seem a lot faster (which is why I ran the test in the first place) surfing. Sites just pop up now.

Of course as always YMMV!

HTH :cheers:

I’m glad to see that your performance is much better with your new modem. A few years back, I upgraded a Comcast DOCCIS 2.0 modem for a newer DOCCIS 3.0 modem (http://en.wikipedia.org/wiki/DOCSIS), which I purchased to avoid paying Comcast a monthly rental fee. I read this entire discussion thread, but a very important point that is missing is the importance of download and upload speed consistency. That’s something that none of the Internet Service Providers seem to mention. Sadly, discussion of this topic was also missing from Patrick Marshall’s leading story of March 5th: “Tools for Testing your Internet Speed” (http://windowssecrets.com/top-story/tools-for-testing-your-internet-speed/).

I’d like to refer folks to the White Papers site at VisualWare (http://www.myconnectionserver.com/resources/index.html). All 5 white papers are worth reading. A connection that gives one a consistent 7 Mbps will be much better than one that has a higher average, but includes lots of variance in the speed. There are analogy’s with the children’s story The Tortoise and the Hare (http://en.wikipedia.org/wiki/The_Tortoise_and_the_Hare). Did the faster Hare win the race? Nope. The Transmission Control Protocol / Internet Protocol (TCP/IP) is sensitive to changes in speed, and resets can occur when speeds are inconsistent. Resets are very expensive as far as user experience is concerned.

I had run tests just before deciding to purchase the modem and was getting about 6Mbits down.
Today after installing the new modem I’m getting 16Mbits down basically for free!

Upload speed about the same at approx: 0.5Mbits.

Things sure seem a lot faster (which is why I ran the test in the first place) surfing. Sites just pop up now.

Of course as always YMMV!

HTH :cheers:

I’m glad to see that your performance is much better with your new modem. A few years back, I upgraded a Comcast DOCCIS 2.0 modem for a newer DOCCIS 3.0 modem (http://en.wikipedia.org/wiki/DOCSIS), which I purchased to avoid paying Comcast a monthly rental fee. There was a measurable performance improvement, when comparing the results before and after upgrading the modem.

I read this entire discussion thread, but a very important point that is missing is the importance of download and upload speed consistency. That’s something that none of the Internet Service Providers seem to mention. Sadly, discussion of this topic was also missing from Patrick Marshall’s leading story of March 5th: “Tools for Testing your Internet Speed” (http://windowssecrets.com/top-story/tools-for-testing-your-internet-speed/).

I’d like to refer folks to the White Papers site at VisualWare (http://www.myconnectionserver.com/resources/index.html). All 5 white papers are worth reading. A connection that gives one a consistent 7 Mbps will be much better than one that is rated 14 Mbps, or even 21 Mbps, but includes lots of variance in the speed. (I’m just using 7, 14 and 21 as examples–the same applies to other multiples of claimed speeds, such as 15, 30 and 45 Mbps). There are analogy’s with the children’s story The Tortoise and the Hare (http://en.wikipedia.org/wiki/The_Tortoise_and_the_Hare). Did the faster Hare win the race? Nope. The Transmission Control Protocol / Internet Protocol (TCP/IP) is sensitive to changes in speed, and resets can occur when speeds are inconsistent. Resets are very expensive as far as user experience is concerned.

I noticed when creating pass-through queries I had to store the DSN info in the ODBC connect string. Is there a way to avoid that as it exposes the password in there.

You can use Access MVP Ben Clothier’s method, which I just recently implemented for an Access reporting tool that uses Pass-Through queries to an Oracle database with Integrated Security:

Power Tip: Improve the security of database connections
http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/

Note: Add a declaration for strConnection in the InitConnect function. By private e-mail, Ben mentioned that he omitted this by accident in the posted copy.

I also recommend that you use DSN-Less (or DSN-Free) connection strings:
http://www.accessmvp.com/djsteele/DSNLessLinks.html

As this is your 1st commercial database, I suggest you employ the use of local experts. It won’t be cheap but you can build a business relationship there.

I agree with this advice from Browni. As a former Microsoft Access MVP (2006 – 2012), I am aware that Access MVP Albert Kallal has an application called RIDES (http://www.kallal.ca/rides/Rides.html), which stands for “Reservation Information Display Enquiry System”. You might want to contact Albert to see if a slight modification could get your desired application up and running much quicker.

Quote from article:

(There’s a common misconception that IPv6 can be removed or disabled. It can’t be removed, but it can be disabled. Because IPv6 is an integral part of the Windows, Microsoft states that disabling it will probably break some Windows components or cause erratic performance. The company believes there is no valid reason to disable IPv6 and strongly advises against it.)

I have IPv6 disabled for my network adapter, and have not had any problems other than the built-in Windows 7 mapping tool no longer working. As far as security-related advice is concerned, I’ll trust Fluke Networks before I trust Microsoft. Please see the following paper:

The Invisible Threat: IPv6 on your network
http://support.flukenetworks.com/portweb/Download/Asset/OptiViewXGNetwo_3381730_6510_ENG_B_W.PDF

Two recommended solutions (I use the AutoFEUpdater Utility):
http://autofeupdater.com/ or
http://www.fmsinc.com/MicrosoftAccess/VersionLauncher.asp

Note that you can sometimes get unexpected results if you attempt to run code from within an Access application that is designed to replace itself, if it detects a newer version. In fact, I used to use such code, but about 1 or 2% of the time, I would have user’s complain that the application was not useable. After switching many years ago to the AutoFEUpdater, I have had no more such problems.

A triangle is to be changed by increasing the length of the base by 40% and decreasing the length of its height by 40%.

Will the new area be change and, if so, will it be increased or decreased and by how much?

I believe the new area will be decreased by 16%.

Let A1 = Original area = 1/2(bh)
Let A2 = New area = 1/2(b x 1.4)(h x 0.6)

Note: 1.4 is a 40% increase in base and 0.6 represents a 40% decrease in height.

New area = 1/2(0.84)(bh)

Therefore, the new area is 84% of the original area, which represents a 16% reduction in area.

Hi Richard,

In Access 2003*, you can experiment with View | Sorting and Grouping
Select the field of interest as the Field/Expression and try changing the Keep Together property to Whole Group. That might give you the effect you desire.

You might also try experimenting with the Section property. For example, with the Detail Section selected, if you look on the Format tab of the Properties sheet, you should see Keep Together property. Try setting it to Yes, to see if this produces the desired result.

* Sorry, I’m not using the newer versions of MS Access on a daily basis, so I cannot tell you how to find the equivalent functionality in A2007, A2010 or A2013. It is there, you just might have to play the Where’s Waldo game to find it.

Building on Richard’s answer just a bit:

Try this

IIF(IS NULL([DOB], “”, Mid([DOB],5,2) & “/” & Right([DOB],2) & “/” & Left([DOB],4))

use the IsNull function, and add a closing parenthesis at the indicated location, so that the count of “(” matches the count of “)”. Also, add an equals sign if this is the Control Source for a text box on a form:

=IIF(ISNULL([DOB]), “”, Mid([DOB],5,2) & “/” & Right([DOB],2) & “/” & Left([DOB],4))

Also, make sure that the name of the text box is not DOB, otherwise, you will get a circular reference error. I recommend naming the control txtDOB. You can use the same expression in a query, but you will need to alias the field name different from DOB, or else you will have the same circular reference error. Something like this, as the Field expression. Note that you omit the equals sign in a query:

Field: CalcDOB: IIf(IsNull([DOB]),””,Mid([DOB],5,2) & “/” & Right([DOB],2) & “/” & Left([DOB],4))

Either way, calculated on-the-fly in a query or setting the Control Source on a form to the expression, you will end up with a read-only result for the calculated DOB value. Instead, you might want to consider running an UPDATE query, to update a new field with data type Date/Time to the actual date, instead of leaving it as a string. This will allow you to easily calculate a person’s age, based on the computer’s system time and the DOB value.

Hi Pillarcas,

Try this modification:

[Code]
Option Compare Database
Option Explicit

Private Sub Combo63_AfterUpdate()
‘–Find the record that matches the control.
Dim rs As Object
Dim strSearch As String

If InStr(1, Me.Combo63, “‘”) > 0 Then ‘ An apostrophe was found in the data
strSearch = Chr(34) & Me.Combo63 & Chr(34)
Else
strSearch = “‘” & Me.Combo63 & “‘”
End If

Debug.Print “[CompanyName] = ” & strSearch

Set rs = Me.Recordset.Clone

rs.FindFirst “[CompanyName] = ” & strSearch
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
[/Code]

This is based on a Microsoft KB article 286242:

ACC2002: Unable to Use FindFirst to Retrieve Value with Apostrophe
http://support.microsoft.com/?id=286242

A better solution, of course, is to provide a primary key (either autonumber or text that does not include special characters) as a hidden column in the combo box row source, and base the search on this key field. For example, if you use the wizard in Access 2003, with a form based on the Customers table, the resulting combo box row source will be this:

SELECT [Customers].[CustomerID], [Customers].[CompanyName] FROM [Customers];

Note: I would add an ORDER BY clause as well:
SELECT [Customers].[CustomerID], [Customers].[CompanyName] FROM [Customers] ORDER BY Customers

or, more simply:
SELECT CustomerID, CompanyName FROM Customers ORDER BY Customers

The combo box has 2 columns, with column widths of 0″;1″ and with bound column = 1
This means that the first column in the SELECT statement, CustomerID, will not be displayed to the user, since the column width is zero. However, it will be used in the AfterUpdate Event procedure, since it is the bound column:

Private Sub Combo63_AfterUpdate()
‘ Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst “[CustomerID] = ‘” & Me![Combo63] & “‘”
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This version, which uses the 5 character text primary key in the Northwind Customers table, will not choke with customer names that include special characters.

Notes:
1) Now would be a good time to rename Combo63 to something more logical, such as cboFindCustomer.
2) Disregard the “ACC2002” in the title, as the same logic applies to all versions of Access
3) The KB author could have done a much better job, instead of just showing a hard-coded solution for a single customer!