MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

[MS Access 2003] Scheduled task for a VBA function

with one comment


Following my previous post, I found myself in need to schedule a MS Access VBA function to run once a day. On the internet we find plenty of articles recommending to launch the Access with the destination DB as parameter and the /X “nameofthefunction” switch.

In my case this did not work for a “cannot find macro” error.
Then what I did is create a macro that would call the function (use the “Run Code” method).
I came up with a couple of generic guidelines dictated by the various problems I bumped into:

1. In the Run Code method specify the function you are calling adding () at the end even if no parameters are expected (e.g.: myfunction())
2. Make sure your function does not have the same name as the module (in my case this caused confusion for the function call)
3. You MUST use a function not a SUB or it won’t work
4. Any warning at the start up of MS Access will prevent the scheduled task from running. For macros you can simply lower the security for Macros.
5. Make sure the references are correct in VBA. In our case the database is copied at regular intervals from another pc. Some DLLs were missing and some other were misplaced.
More on this: I had a weird problem: although I had referenced and installed the same DLL on the server and the client, when the db was copied over from the client, I would still get a “MISSING” DLL error (and the name and location were the same). It turns out that the “references” take into account not only the file name and path but even the file version (although not visible). After copying the referenced DLL over in the same location, everything worked.

HTH,
Roberto.

Written by zantoro

November 18, 2013 at 2:19 pm

Posted in MS Access

Querying a MS Access 2003 database from SQL Server 2008

with one comment


Hello,

I spent the last few days trying to get an SQL Server 2008 to work and sync with queries hosted on an old, heavy and complicated MS Access 2003 (~1Gb of data).

This is not a success story but after wasting so much time on a failure, I feel like sharing my findings.

Let’s start saying that you can’t install Jet 4.0, the native MS Access 2003 driver, on a 64bit SQL Server but you can install SQL Server 32bit (Express edition) on a Windows Server 2008 R2 (they only make this in the x64 flavour).

It doesn’t make a difference if you create the linked server with a straight connection to Access (using the ACE OLEDB 12 driver ) or if you use an ODBC connection that use that driver (it might be obvious for some, not for me…)

So first I thought I’d simply install Access Database Engine 2010 and use the ACE OLEDB 12 driver to retrieve my data, you will have to make queries as this one:

select *
from openquery([LinkedServer], ‘select * from AccessQuery’)

This works for simple queries to tables or even MS Access queries but, as soon as the query uses a custom function (i.e.: developed in VBA), you’ll get the following error:

OLE DB provider “MSDASQL” for linked server “SMGODBC” returned message “[Microsoft][ODBC Microsoft Access Driver] Undefined function ‘customfunction’ in expression.”.

FYI: you get the same error if you have a MS Access built-in function such as replace or Mid but you can get around this by disabling the sandbox mode.

Some people, problems like this could be fixed by installing Access Database Engine 32bit (Note that you will have to uninstall every 64bit Office app you have on your machine)… but I got the same errors anyway.

So I thought I’d try to install Jet 4.0 hoping the queries would get executed “server side” (on MS Access) thus recognising the “custom function”.

I installed an SQL Server Express x86 (32bit) on my server and now, installing Access Database Engine 2010 you will also see the MS Jet 4.0 provider. Unfortunately, in my case, using this provider to exec queries resulted in a timeout every time… but maybe this is due to the exceptional size of our database (so if your DB is smaller do give it a try and let me know).

Then I tried using the ACE 12 driver and replacing custom functions with SQL Server functions (I only had 6 to create/convert from VBA), but I got another blocking problem, 3 of the 28 queries I had to retrieve from MS Access used linked tables to the same server I was querying from (ironic isn’t it??).

I could have re-engineered the queries eliminating joins to linked tables in the MS Access queries and joining them directly in the openquery statement but these queries are rather complex and this task would be time consuming so in the end, due to time constraints, I resolved to push the queries directly from Access to SQL Server tables using the TransferDatabase method which I scheduled to execute once a day.

From all this I feel we can extrapolate a couple of guidelines to consider before you waste too much time trying to do the same:

  1. The Jet 4.0 driver seems to time-out with big MS Access databases
  2. You can use the ACE OLEDB 12 driver to query Access from any version of SQL Server but watch out for custom functions and/or linked tables joins as you will have to modify your queries both on MS Access and SQL Server to get it working.

HTH,

Roberto.

Written by zantoro

November 11, 2013 at 9:21 am

Posted in MS Access, SQL Server

Tagged with ,

Buying an Android S4 Clone… not my best idea! Malwares all over, tough to remove

with 4 comments


a couple of months ago I told to myself why spend 550 euro to buy a Samsung S4 when you can get the same software and very similar hardware for ~200?

So I went on Amazon and I bought this shiny, Chinese made new phone that looked exactly like an S4 (the model in this case was the HT-9500 but these clones are all the same).

The hardware and screen are great although the power button came off approx. 2 months after I bought it… but that was not the real turn off. The biggest annoyance were some recurring ads and random Chinese apps installed without my consent and whose provenance I couldn’t not trace.

At first I lazed around, trying to get it cleaned up using main stream Anti Viruses like AVG, Avast and Lookout… These last two didn’t even notice anything wrong, AVG at least was able to identify a malicious “Play Store” which resembled a lot the genuine “Google Play Store”.
I tried removing it through the AV console and the “manage apps” menu without luck: in both cases I got an error saying “uninstall unsuccessful”.

A little search on Google for the “Play Store” filename, uuplay.apk, returned few articles on how this Malware spreads.

To remove it, you will have to root your Android (FYI: rooting an Android mobile means enabling the all-powerful root user thus granting you special powers, such as deleting system files… and our uuplay.apk is marked as system file).

Info: Rooting, and this is only my understanding, is nothing more than connecting from your computer to Android with an app called ADB and push 3 apps (busybox, pwn and su) to the device. Those apps will then allow the user to take full control of the phone.

Now, rooting my Android device for me was not as easy as I had expected by reading several articles but this could be due to the fact that I was trying to use my Win7 VM running under VMware Fusion (on Mac).

No matter what rooting kit I used, or which ADB drivers I installed, the script would always fail with an “Access Denied”. For your reference, here are the rooting kits and drivers I tried out:
Rooting kits: SuperOneClick, mt6589_rooter, Motochopper_Auto_Root
ADB Drivers (you need these to connect and push the apps): “adt-bundle-windows-x86_64-20130917”

In the end, I managed to root it by doing it directly from Mac. The procedure in that case was fairly simple: Install the Android SDK and the platform-tools with it… The platform-tools come with ADB. After that I copied the content of Motochopper_Auto_Root content in that same folder and executed ./run.sh from the terminal.
It failed a first time as it couldn’t find the 3 apps but once I placed them where the script said it was looking for them, it worked like a charm.

As my mobile was officially rooted, I proceeded to the clean up:
I installed an app “ES File Explorer” which allows browsing and deletion of files (even system’s) using root powers (but you have to enable “root explorer” in the application settings).
Then I browsed /System/Apps and found my precious uuplay.apk and deleted it (one of the articles on the subject mentioned 2 more files but I only found the one).

The phone seems clean now but with Malware we never really know, I’ll keep you posted.

HTH,

Roberto.

Written by zantoro

October 13, 2013 at 9:00 pm

Posted in Android

Tagged with , ,

IIS7.5: Could not load file or assembly ‘AjaxControlToolkit’ or one of its dependencies. Access is denied.

leave a comment »


Capture

I’m writing a quick post about this as the info I found on the internet were quite confusing and often superficial…

From my experience, most of the times, this error is due to some missing NTFS rights on a folder for the user that you impersonate when visiting a website (in most cases either the anonymous user or the application pool identity).

Most people tell you to click here and there, adding rights for random users… I suggest to take a more professional (and simple) approach.

  1. Download “Process monitor” (sysinternals)
  2. Add a filter to show log activity only for the PID of the W3WP.exe process running your website (e.g.: process IS 764)
  3. Reproduce the problem
  4. Stop the monitoring and search for “Denied”

You’ll find a line that says which folder is causing the error, the user and the kind of access it requires.

Add that right, no more no less to the folder reported and you should be good to go…

HTH,

Roberto.

Written by zantoro

October 9, 2013 at 4:42 pm

Posted in IIS

Tagged with , ,

DotNetNuke – Upgrade to 7.1.1 – DDRMenu doesn’t work anymore

with 5 comments


Hello,

After upgrading DNN PE to 7.1.1, the DDRMenu suddenly stopped working due to a JS error: “Uncaught TypeError: Object function (e,t){return new b.fn.init(e,t,r)} has no method ‘curCSS'”.

the JQuery CurCSS method has been deprecated in JQuery 1.8.

DNN 7.0.x used JQuery 1.7 while DNN 7.1.x uses JQuery 1.9.1

The DDRMenu has not been updated to work with new versions of JQuery and they don’t seem to have a new version in the pipe on their website http://dnnddrmenu.codeplex.com/

The fix is quite easy but it might get overridden next time we upgrade the DDRMenu:

Edit \DesktopModules\DDRMenu\DNNMenu\DNNMenu.min.js and replace all “curCSS” method calls (2) with “css”

Update: DNN support replied that everything worked fine with a “out of the box” skin and therefore the problem was caused by my custom skin which was out of support (they don’t give support on Skin development).
By comparing my skin with one of the OotB, we found out that we were using <DNN:NAV instead of the more common <DNN:MENU. The DNN:NAV should still work but I get a feeling that is slowly dying (I can only find documents related of this menu in connection with DNN4 and 5, nobody seems to have used it since…)

We will investigate further but in the meantime, we are considering switching our menu to <DNN:MENU

Update2: Replacing the DNN:NAV with a DNN:MENU was easier than I thought and I get the feeling this incompatibility is the tip of the iceberg. Your safest bet is to download a free, prebuilt menu (superfish works pretty well out of the box), copy it in the skin folder and link it from the template (menustyle) and tweak the look and feel.
FYI: here is what they write on the DNN website about DNN:NAV:
Note that while it is also possible to use a dnn:NAV control with the DDRMenuNavigationProvider and CustomAttributes to render a non-DNNMenu template, it adds complexity and brings no particular benefits, so is not recommended.

HTH,

Roberto.

Written by zantoro

September 18, 2013 at 12:01 pm

Posted in DotNetNuke

Tagged with , , ,

ASP.NET Ticket expiration and Forms timeout

leave a comment »


Hello,

we have an internally developed .NET 2.0 CMS that makes heavy use of Forms authentication.

On a customer’s site we experienced random downtimes (~ twice per month) due to a maxed out pool of connections to the Database server (SQL Server).

Error : System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached

When I looked at the IIS logs of the website, around the time of the crash, I could see repeated anonymous calls to pages from the same IP.

Initially I thought of a DoS attack, but then I realised that the same IP, not too long before had been used by an authenticated user.

Then I thought, the user must have a virus, but then I comparing it the other downtimes I saw that the users were different each time.

This is when I realized that out application event log was filled with this info message:

Event message: Forms authentication failed for the request. Reason: The ticket supplied has expired.

At this stage I started researching how forms authentication times out on .NET 2.0.

As far as I can see, there are 2 timeouts: the ticket expiration and the Forms timeout.

What happened in our site is that the ticket was set to 60 minutes and the Forms timeout (web.config) to 90.

If a user would try to browse in the span of time between minute 61 and 89, his/her browser would start an infinite loop trying to go back to the private homepage which it could not access as the ticket was expired.

This infinite loop caused the user to exhaust the website’s Database connections.

I even managed to reproduce the problem and get the infinite loop error (HTTP 310).

There are still unanswered questions like:

– Why did the website loop instead of kicking the user out?

– Why didn’t the ticket get renewed although in the web.config it is set to slidingExpiration=”true”

For the moment the Developers and I have agreed to set ticket expiration and forms timeout to the same value and the problem seems to have stopped. I hope I’ll be able to answer those other questions soon.

One more tip: according to my research, these are the only 2 values in .NET that allows you to extend a user authentication. Our customers use these CMS as collaboration tools and often demand to have it set to several hours. We did some testing and saw no drawbacks in having both set to 600 minutes.

One more thing: I’ve also asked our developers to make the ticket expiration and application variable (web.config) so that we can tweak it without having to edit the login page.

HTH,

Roberto.

Written by zantoro

July 30, 2013 at 10:03 am

Posted in IIS

Tagged with , ,

Joomla: country content filter – custom plugin

leave a comment »


Hello,

one of our customers kept complaining that users connecting from China get an ugly 404 error in the iframe where we displayed our Vimeo videos.

We contacted Vimeo and their obvious answer was: “we have no control on this, we can’t help you”.

We went looking for a plugin that would allow us to display content based on the user provenance but we were unable to find anything that would fit our requirements so we developed it ourselves (the credit goes mostly to Tom Meredith who re-engineered my initial prototype from scratch).

This simple plugin allows editors to easily decide what content should be displayed for selected countries based on the visitor’s IP, for example:

{countryaccess country=CN,MM}
I am not in China or Myanmar
Here you put the code you want to display if the user is not from China or Myanmar
||
I am in China or Myanmar
Here you put the code you want to display if the user is from the countries listed in the opening tag (country=). For example, with our customer we decided to display a nice image that would redirect the user to the Vimeo channel (he would still get a 404 then but not on our homepage).
{/countryaccess}

The cool thing is that the plugin can be reused for all sort of things: localised Ads, localised content etc.

The plugin can be downloaded from: https://sourceforge.net/projects/countryaccess/?source=dlp

HTH,
Roberto.

Written by zantoro

July 9, 2013 at 3:15 pm

Posted in Joomla

Tagged with