[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Access 2000

I have an access 2000 file format front end database which is run in Access 2007, tables are held in a back end SQL Server database and are linked to the front end. The front end is kept in a folder on a shared network drive where everyone accesses it.  I would like to upgrade the DB to Access 2007/2010, however when I have tried to do this, the following problem presents itself.

A lot of my queries/reports are opened via a popup form which allows users to select an option which then filters the resulting query/report by the chosen option. After the upgrade, the popup still works on my PC but will not popup on another PC when trying to open the same report.  The database locks up and I have to force a quit.  

Can anyone assist please?
0
andymacf
Asked:
andymacf
  • 3
  • 3
1 Solution
 
GrahamMandenoCommented:
Hi Andy

Start by looking for differences on the two systems: Same version of Access? Same operating system? Same permissions on the SQL Server database? ... etc.

Also: Does the code compile OK? Does your front-end use DAO or ADO (or both)?  What references does your VBA project have, and in what order?  Does it include any "foreign" DLLs or ActiveX controls?

-- Graham [Access MVP since 1996]
0
 
andymacfAuthor Commented:
Hi Graham
Thanks for the quick response. I will check these tomorrow, being in UK it is 2320 and too late to start looking now. Several things I do know for sure - same version of Access & operating system, I do have greater level of permissions compared to anyone else.  I could also paste an example if my code tomorrow if that would help?

Rgds
Andy
0
 
GrahamMandenoCommented:
Hi Andy

Don't bother posting any code (yet!)  Just check that the code compiles (Debug > Compile project) and check the references (Tools > References) for any "MISSING" references or other anomalies.

One thing that happens when you upgrade to ACCDB format is that the DAO 3.x reference is replaced by the "MS Office xx.0 Access database engine object library" (ADE) and it gets placed at the bottom of the list.  Moving it up to its normal 3rd place often helps, especially if you also have a reference to ADO.

I'm in New Zealand, so I can see we might have a few time-zone lags :-)

Cheers,
Graham
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
andymacfAuthor Commented:
Hi Graham

Would you mind explaining DAO & ADO a bit more and where I would check for this?

I have moved "MS Office xx.0 Access database engine object library" item to position 3 in the list and I think, not wholly confident, that this has sorted my issue.

Does it include any "foreign" DLLs or ActiveX controls?  Could you also please clarify where I would check this?

Thanks again
Andy
0
 
GrahamMandenoCommented:
Hi Andy

DAO is the "native" set of objects for accessing a jet database (MDB).  It appears in the reference list as "Microsoft DAO x.x Object Library"

ADO is a set of objects that was (stupidly IMNSHO) introduced in Access 2000 that was meant to be more universal in accessing other database technologies.  It was also the basis of the now deprecated ADP technology.  It appears in the reference list as "Microsoft ActiveX Data Objects x.x Library".  Unless it is required, it should be un-ticked to remove it from the reference list.

ADE is the replacement for DAO, introduced with Access 2007, to deal with the new format ACCDB Jet databases.  (It is required if you want to use suct abominations at the new-fangled "multi-value fields").  It appears in the reference list as "MS Office x.x Access database engine object library".

DAO and ADO share quite a number of object names (for example, "Recordset" and "Field"), so if you have both in your reference list and you declare, say, "Dim rs as Recordset", it will create either a DAO.Recordset or an ADODB.Recordset, depending on which reference is higher up the list.  This is why converting a database with both references often causes problems, because the order of references and therefore the default library can change.

"Foreign DLLs or ActiveX controls" include any other references in your list, apart from the first two, which must be there (VBA and MS Access) and "OLE Automation".  You may well have these present if you use ActiveX controls on your forms (such as TreeView or FlexGrid), or if you include references to external code libraries.

Cheers,
Graham
0
 
andymacfAuthor Commented:
I have been trying to sort this problem for months, then along comes Graham with a spot-on answer and a full explanation of what happens during an upgrade from 2000 to 2007.  I am a very happy chap.  Thanks Graham, much appreciated
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now