related sites: <FREE Help> <ASP> <Asp.net> <worldwide> feedback: <lovethat> <hatethat> <thanks> <credits> <contact us>
Multitable queries w/same field names <Next>
From: Scott W. Smith swsmith@ccnet.com Date: Wednesday, April 08, 1998 11:07 AM Subject: DSN Less Connection
This post hopefully answers most of the issues raised in Scott Smith's message of April 8 concerning DSN Less Connection. The questions mainly focussed around connection string parameters. In most cases I have asked other wider questions that encompass Scott's original questions.
GENERAL CONNECTION STRING QUESTIONS
1. What connection string arguments are supported by ADO?
Surprisingly there are only five supported arguments:
Either of the following ADO connection strings could be used to open a database connection (in this case Oracle)
2. I guess that "Provider=MSDASQL" can be omitted.
Yes. MSDASQL is the default provider for ADO therefore if the Provider argument is omitted ADO will automatically attempt to establish a connection to this provider. NB MSDASQL is the Microsoft OLE DB provider for ODBC.
3. If ADO only supports 5 connection string arguments what happens to any other arguments I specify?
All other arguments are passed to the OLEDB provider. MSDASQL itself does not support any specific connection parameters in addition to those defined by ADO. However, it does pass the connection parameters to the ODBC driver manager.
4. So what is the impact of the MSDASQL provider being the default driver and all arguments being passed to the ODBC driver manager?
The ADO connection string you create can be identical to ODBC connections strings for the same source. This is why you see so many connections strings with DSN, FILEDSN, UID and PWD arguments rather than the ADO arguments outlined above.
6. I use the session variable Session("ConnectionString") to store my connection string some others I have seen use "DataConn_ConnectionString", I am not sure why?
Session("DataConn_ConnectionString") is the variable used/created by the wizards within Visual Interdev. Either approach is perfectly valid. It simply depends on the variable naming standards you have chosen to adopt. A list of ASP standards can be found in Appendix B of the MS Internet Information Server Resource Kit (MS Press ISBN 1-57231-638-1)
ACCESS SPECIFIC CONNECTION STRING QUESTIONS
As previously mentioned any other connection string arguments are passed to the provider/ODBC driver manager. The balance of these questions relate specifically to Access data sources :
7. When specifying a DSN-Less connection to an Access database what arguments are compulsory?
The driver and DBQ are the only required arguments.
The driver argument supplies the name of the ODBC driver. The DBQ argument supplies the name of the database file the following string would open database test.mdb which resides in the physical directory D:\Inetpub\wwwroot\testweb\ :
cnTest.Open ("driver={Microsoft Access Driver (*.mdb)};DBQ=D:\Inetpub\wwwroot\testweb\test.mdb")
When using an ISP is it good practice to use Server.Mappath to determine the directory. This will ensure your application continues to function in the event that you move to another ISP or the ISP changes the directory structure etc. You should open the connection using something like the following :
cnTest.Open ("driver={Microsoft Access Driver (*.mdb)};DBQ=" & server.mappath("test.mdb"))
8. What other arguments are supported when connecting to Access Databases and what do they mean?
DRIVERID
An Integer ID for the driver. The driver ID for access is 25. It is not required since all Access databases use the same driver irrespective of version. Other data sources such as Excel have different driver IDs for different versions of Excel for example the Microsoft Excel 97 driver ID is 790, the Excel 3.0/4.0 ID is 278.
The following connection string demonstrates the use of driver ID with an access database:
cnTest.Open ("driver={Microsoft Access Driver (*.mdb)};DBQ=c:\test.mdb;DriverId=25")
FIL
File Type. This argument is supported for backward compatibility and should not be used. The file type for Access is MSAccess.
UID
The user ID. The user ID is not required for unsecured Access databases as it defaults to the "Admin" user. However, If your database has been secured you should use the UID to specify the username.
PWD
The user-specified password. The PWD argument is not required for unsecured Access databases as Access does not initially set a password for the Admin user. As for UID the PWD argument should be passed when dealing with secured databases.
MAXBUFFERSIZE
The size of the internal buffer in kilobytes that is used by Access to transfer data to/from the disk. The default size is 512K, any multiple of 256 should be specified.
The jet engine reads and writes data in 2K pages, placing data in a temporary cache. When performing add, delete or updates (that are not part of a transaction) Jet temporarily saves changes to the cache. These are periodically written as a chunk to the database or when the cache reaches the MAXBUFFERSIZE. Additionally, Jet minimises the time spent reading data by utilising the cache.
I suspect the only way to optimise this setting is to perform a series of benchmarks on the system using different values. However some general rules are
PAGETIMEOUT
The period of time, in tenths of a second, that a page (if not used) remains in the buffer before being used. The default for Access is 5 tenths of a second. Increasing the time out can improve performance as more pages will be read from the cache, however it does have the disadvantage that pages may not always be up to date.
READONLY
Specifies whether or not data source is read only - Default is read/write data source.
cnTest.Open ("driver={Microsoft Access Driver (*.mdb)};DBQ=c:\test.mdb;READONLY=1")
Read/Write data Source
cnTest.Open ("driver={Microsoft Access Driver (*.mdb)};DBQ=c:\test.mdb;READONLY=0")
SYSTEMDB
The path specification to the system database file.
THREADS
The number of background threads for the engine to use default is 3.
Jet uses threads for Read Ahead Caching(transaction commit), Cache maintenance and detecting changes to a shared database. Increasing the threads value can improve performance particularly for databases with lots of activity or databases with large numbers of linked tables.
IMPLICITCOMMITSYNC
Specifies whether driver will perform internal or implicit commits asynchronously, the default is Yes
USERCOMMITSYNC
Specifies whether driver will perform user-defined transactions asynchronously, the default is Yes.
9 What's the difference between synchronous and asynchronous mode?
In synchronous mode, Jet doesn't return control to ASP until changes are written to the database i.e. after the Update method is used or CommitTrans if transactions are used.
In asynchronous mode the changes are stored in the cache, control is returned to ASP immediately and changes are then written in a background thread after a specified period of time (PAGETIMEOUT) or when the MAXBUFFERSIZE is reached.
I based my response on my personal notes though as I recall my initial sources for these answers were primarily the Internet SDK, ODBC SDK and VB product information.
regards, Steve