how to connect from vbs code to local MS Access database
in [Visual Basic]
|
Prev: MAPI Issue
Next: Register a file extension
From: CS student on 7 Apr 2010 07:20 Hi, I try to write some code that will connect from a simple local vbs script, to a local MS Access database, for read-only purposes. This is the code that works OK for me when opening an MDB Access database: -------------------------------------------------------------------------------------------------------------- Set CN = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.RecordSet") CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c: \my_database.mdb" RS.CursorLocation = 3 RS.Open "SELECT * FROM MyTable",CN do while RS.EOF = false .... -------------------------------------------------------------------------------------------------------------- Now, when using the same code but with MS ACCESS 2007 local databse, i.e., instead of source=c:\my_database.mdb i write source=c: \my_database.accdb i get an error from the Microsoft Jet Database engine: Unrecognized database format source=c:\my_database.accdb Can you please tell how to chnage my code to support MS ACCESS 2007 databases? Also, i read that in real-life, the Jet engine can supprot upto 10 concurrent connections. In case i will open the same local database for read-only purpoeses by more than 10 connections concurrently (invoke this vbs several times concurrently), will it stop working, or will it work too slow? Thanks a lot for the help
From: ralph on 7 Apr 2010 08:43 On Wed, 7 Apr 2010 04:20:38 -0700 (PDT), CS student <israelaix(a)hotmail.com> wrote: >Hi, > >I try to write some code that will connect from a simple local vbs >script, to a local MS Access database, for read-only purposes. > >This is the code that works OK for me when opening an MDB Access >database: >-------------------------------------------------------------------------------------------------------------- >Set CN = CreateObject("ADODB.Connection") >Set RS = CreateObject("ADODB.RecordSet") > >CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c: >\my_database.mdb" > >RS.CursorLocation = 3 >RS.Open "SELECT * FROM MyTable",CN > >do while RS.EOF = false >... >-------------------------------------------------------------------------------------------------------------- > 'Change the Provider to "Microsoft.ACE.OLEDB.12.0" for 'Access 2007 ACCDB databases. as show in ... http://support.microsoft.com/kb/283874 -ralph
From: ralph on 7 Apr 2010 09:05 On Wed, 7 Apr 2010 04:20:38 -0700 (PDT), CS student <israelaix(a)hotmail.com> wrote: > >Also, i read that in real-life, the Jet engine can supprot upto 10 >concurrent connections. In case i will open the same local database >for read-only purpoeses by more than 10 connections concurrently >(invoke this vbs several times concurrently), will it stop working, or >will it work too slow? > Not sure where you read that, as all Jet formats can theoretically support up to 255 concurrent users. However the practical limit is often much lower, and depends on how and what your are doing. The results from exceeding the limitations in your particular problem domain can run from simply slowing down to data corruption. It all depends. For more information on Jet 4.0 and below ... "How to keep a Jet 4.0 database in top working condition" http://support.microsoft.com/kb/303528 I have not seen a similar summary article on the newer ACCDB format. Probably because the new format was not intended to be used beyond the MSAccess/Office environment. (When first released one couldn't even distribute an OLE DB provider.) However since it is essentually a file-based database, the same as the Jet Formats that came before, I suspect the limitations and warnings would be much the same. hth -ralph
From: Mayayana on 7 Apr 2010 10:27 Also note, this is not a VBScript or Access group. (VB is not the same as VBScript.) This is a VB group. For VBScript try here: microsoft.public.scripting.vbscript There are also a number of Access-specific groups: microsoft.public.access microsoft.public.access.*
From: Paul Clement on 8 Apr 2010 08:23
On Wed, 7 Apr 2010 04:20:38 -0700 (PDT), CS student <israelaix(a)hotmail.com> wrote: � Hi, � � I try to write some code that will connect from a simple local vbs � script, to a local MS Access database, for read-only purposes. � � This is the code that works OK for me when opening an MDB Access � database: � -------------------------------------------------------------------------------------------------------------- � Set CN = CreateObject("ADODB.Connection") � Set RS = CreateObject("ADODB.RecordSet") � � CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data source=c: � \my_database.mdb" � � RS.CursorLocation = 3 � RS.Open "SELECT * FROM MyTable",CN � � do while RS.EOF = false � ... � -------------------------------------------------------------------------------------------------------------- � � Now, when using the same code but with MS ACCESS 2007 local databse, � i.e., instead of source=c:\my_database.mdb i write source=c: � \my_database.accdb i get an error from the Microsoft Jet Database � engine: Unrecognized database format source=c:\my_database.accdb Below is a link to connection string examples for Access 2007. You may also need to install the ACE OLEDB Provider if Office is not installed on the target machine: http://www.connectionstrings.com/access-2007 http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en � Also, i read that in real-life, the Jet engine can supprot upto 10 � concurrent connections. In case i will open the same local database � for read-only purpoeses by more than 10 connections concurrently � (invoke this vbs several times concurrently), will it stop working, or � will it work too slow? I wouldn't expect concurrency issues if the database is being opened read-only for this few connections. Paul ~~~~ Microsoft MVP (Visual Basic) |