[thelist] ms access website solution?

Ken Schaefer ken at adOpenStatic.com
Fri Apr 30 22:43:56 CDT 2004


Hi David,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <david.landy at somerfield.co.uk>
Subject: RE: [thelist] ms access website solution?


: 1. Yes, correct - I'm talking about the back-end, i.e. Jet. 
: My experience: a while ago, I had a server running several 
: ASP sites with Access (read: mdb) backends. The mdb's were 
: created in Access 97 using Jet 3.5. Upgrading to the latest MDAC 
: on my server (as was recommended) suddenly rendered the mdb's 
: un-writable via ODBC. This was a known "feature" of the release,
: but there was no warning on the wrapper that this would 
: happen. Grrr... I had to find that information by searching the 
: web afterwards.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have never heard of this happening, and I've supported a lot of people using older Access .mdb files. What exactly happened? What did you find on the web? What version of MDAC are we talking about?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: The fixes were to upgrade the mdb's to Jet 4 which would have 
: required me buying Access 2000, or so I thought.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The Jet v4 OLEDB Provider and Jet v4 ODBC Driver will happily connect to v3 databases. Alternatively, you can keep using the v3.51 Jet OLEDB Provider. There's no reason you need to "upgrade" to Access2000

If you just want to convert them to v4, you can use JRO (Jet Replication Objects), which has a "Compact and Repair" method. When calling this, you specify what format you want the resulting file to be (either v3, or v4).

http://support.microsoft.com/?id=230501
HOWTO: Compact Microsoft Access Database Through ADO

http://support.microsoft.com/?id=306287
HOW TO: Compact a Microsoft Access Database by Using Visual Basic .NET
(If you are using ASP.Net)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Can you create a Jet 4 mdb file from scrath via ODBC? If, so, how? 
: In any case I would have had to have 2000 to manipulate the mdb's 
: afterwards (they wouldn't have been openable in Access 97). 
: The alternative was to roll back the upgrade... but there was 
: no "uninstall" on the MDAC upgrade! Eventually I managed to 
: undo the damage, but it took a while. That was a frustrating, 
: and annoying experience.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For starters, use OLEDB, not ODBC. ODBC is now listed as a deprecated technology. That said, you can use ADOX (ADO Extensions) to create a new .mdb file (Catalog.Create() method). You can then use either: ADOX to create tables etc, or use SQL statements (e.g. CREATE TABLE).

ADOX class heirachy:
http://msdn.microsoft.com/library/?url=/library/en-us/ado270/htm/admscobjectmodel.asp?frame=true

Or, you can create your databases in Access97, and then use JRO to upgrade to Access2000.


If you want to keep your software costs down, consider registering as a Microsoft Partner, and get the Microsoft Action Pack. You get a heap of software (guides, tools etc) for US$299/year (not sure what the UK cost is). I have included a list of software at the end of this email. It's also available here, but I don't know if this is inside the "secure" partner site or not (if it is, then I'm sure there's also an unsecured page someplace as well, but the below is what I have bookmarked)
http://members.microsoft.com/partner/premium/salesmarketing/partnermarket/actionpack/actionpack_standard.aspx

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: 2. Sounds like I boobed about limited database connections 
: on Jet! I think I may have confused it with my experience of 
: PWS (Personal Web Server) and a limited number concurrent 
: connections on that? 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PWS supports 10 concurrent HTTP connections. This is unrelated to ODBC or OLEDB connections from an application to the database engine. Jet v4 (and I suspect v3) supports a theoretical maximum of 256 connections (you can look this up on the online help). On a practical level this never happens, because some people will be wanting to do updates, and they will lock part of the database to perform the update, preventing others from getting a lock.

Cheers
Ken

[1] Action Pack Standard, NFD (Not for distribution software) for use in your business for training/development/testing/etc type tasks.
      Product Title 
     # of Licenses 
     # of Client Access Licenses 
     
      Microsoft Windows Server 2003 Enterprise Edition 
     1 
     10 
     
      Microsoft Windows Server 2003 Web Edition 
     1 
     10 
     
      Microsoft Windows Server 2003 Terminal Services 
     Not Applicable 
     10 
     
      Microsoft Exchange 2003 Enterprise Server 
     1 
     10 
     
      Microsoft SQL Server 2000 Developer Edition 
     1 
     10 
     
      Microsoft Windows Small Business Server 2003 
     1 
     10 
     
      Microsoft Internet Security and Acceleration Server 2000 Enterprise Edition 
     1 
     Not Applicable 
     
      Microsoft Mobile Information Server 2002 Enterprise Edition 
     1 
     Not Applicable 
     
      Microsoft SharePoint Portal Server 2001 
     1 
     10 
     
      Microsoft Windows XP Professional 
     10 
     Not Applicable 
     
      Microsoft Office Professional 2003 
     10 
     Not Applicable 
     
      Microsoft Office FrontPage 2003 
     10 
     Not Applicable 
     
      Microsoft Office Outlook 2003 
     10 
     Not Applicable 
     
      Microsoft Office MapPoint 2004 
     10 
     Not Applicable 
     
      Microsoft Office Visio Professional 2003 
     10 
     Not Applicable 
     
      Microsoft Office Project Professional 2003 
     10 
     Not Applicable 
     
      Microsoft Office Publisher 2003 
     10 
     Not Applicable 
     
      Microsoft Business Solutions CRM Standard Edition 
     1 
     5 
     


More information about the thelist mailing list