What's the proper way to connect to a database

[Eek]

I've seen at least 5 differents set of code to connetc to an Access Database....Is the code below OK..someone in a previous post told me to remove the section in BOLD becuase it was not necessary...





Dim adoCon 'Holds the Database Connection Object
Dim rsGuestbook 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
dim IntNumber

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Survey.mdb")


'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblComments.* FROM tblComments;"

'Open the recordset with the SQL query
rsGuestbook.Open strSQL, adoCon

 

[snowboardr]

Well the bold part is DSN, and they line right above that is DSN-Less connection, dsn is basicly setting the connection to the database via Data Sources (ODBC) in Control Panel > Administrator tools > Data Sources (ODBC) this usually isnt the best way to do things, some people say its faster, buts not faster then dsn-less.

I can't tell you the best way to do a connection in Access because i never use it anymore, been years since i have. Personally if your host offers it I would switch to MySQL. Its much faster, your database wont become corrupt when it has to much data, and there are less errors.

- Jason

www.sitesd.com
ASP WEB DEVELOPMENT

 

[Tarwn]

I generally stick with DSN-less as well just because itmakes things much more portable (if I move it to another machine and keep the paths the same, everything still works).

Two other things to note however,
1) Selecting * is bad because it requires the database to basically do a pre-query to figure out what fields to replace that star with
2) I've found Recordset.Open is generally less efficient than using Connection.Execute.

There area couple FAQs you might want to look through for this forum that have info on increasing code efficiency and so on. WQhat you have now works fine, but I always end up going back and playing with my code a little to make it faster

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Help, the rampaging, spear-waving, rabid network gnomes are after me!