Connecting from asp to a microsoft access database.

[SpreadTooThin]

I had a web site that was hosted by an ISP a while ago.
The ISP setup the database connection on his host and my ASP pages
were able to access the database as he was also hosting those..

Now I have installed IIS on my server and am hosting the web pages and
database myself.

The thing I don't understand is setting up the database connection.
The file is an mdb file.

The asp pages open the database as follows:

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\inetpub\wwwroot\mydatabase.mdb"

However I am not able to connect to the database and it probably has
something to do with not having setup the operating system to 'serve'
that database.

On Win2k how does one do that?

 

[Arvin Meyer [MVP]]

Try setting up an include which has the DSN (Data Source Name) built in the
ODBC applet (Administrative Tools) in the Server control panel. The use the
include file like:

<%
dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DSN=MyDB"
%>

where MyDB is the name you used when you build the DSN.

 

[Paul Shapiro]

Get the complete asp error message and post it here.

You can check the file and folder security settings. The account under which
the website is running needs Modify access to the folder and the db file so
it can create the .ldb file when it opens the db. You can also look in the
Windows event log for detailed messages.

 

[SpreadTooThin]

Try setting up an include which has the DSN (Data Source Name) built in the
ODBC applet (Administrative Tools) in the Server control panel. The use the
include file like:

<%
dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open "DSN=MyDB"
%>

where MyDB is the name you used when you build the DSN.

I wonder if you might be more specific about how to setup the database
in the Administrative Tools, Data Sources (ODBC) please.

 

[SpreadTooThin]

Get the complete asp error message and post it here.

You can check the file and folder security settings. The account under which
the website is running needs Modify access to the folder and the db file so
it can create the .ldb file when it opens the db. You can also look in the
Windows event log for detailed messages.

I set the permission from the root all the way to the mdb file to
Internet guest account, full control.
(This worries me because it sounds like a security risk, but I have to
get this going.)
Still no luck, and nothing in the event logs at all.
The exact error message is:
Err=-2147217900

 

[SpreadTooThin]

Get the complete asp error message and post it here.

You can check the file and folder security settings. The account under which
the website is running needs Modify access to the folder and the db file so
it can create the .ldb file when it opens the db. You can also look in the
Windows event log for detailed messages.

The asp file logs this:

INSERT INTO clientTable (SFirstName, SLastName, SAddress, SApt, SCity,
SStateProvince, SPostalCode, SCountry, STelephone, SEmailAddress,
DFirstName, DLastName, DAddress, DApt, DCity, DStateProvince,
DPostalCode, DCountry, DTelephone, DCallTime, DAmPm, DRetryInterval,
HName1, HTelephone1, HName2, HTelephone2, HName3, HTelephone3,
DSuspend, DTreatBusyAsWell) VALUES ('bull', 'shot', '1', '',
'calgary', 'ab', 'a2b2c3', 'canada', '4035551212', '(e-mail address removed)',
'calf', 'shot', '2', '', 'Calgary', 'ab', 'a2b2c3', 'Canada',
'4035551313', '1', 'AM', '15', 'ca1', '4035551414', 'ca2',
'4035551515', 'ca3', '4035551616','off','off')
Err = -2147217900

 

[SpreadTooThin]

For what it's worth, I am able to READ the database I just can't write
it.

 

[Arvin Meyer [MVP]]

I wonder if you might be more specific about how to setup the database
in the Administrative Tools, Data Sources (ODBC) please.
-----------------------------------
Control Panel >>> Administrative Tools >>> Data Sources (ODBC)

Then click on System DSN, then the Add button, Select SQL-Server from the
list.

Give the DSN a name, then a description if you like, and tell it where the
data file is.

 

[SpreadTooThin]

I wonder if you might be more specific about how to setup the database
in the Administrative Tools, Data Sources (ODBC) please.
-----------------------------------
Control Panel >>> Administrative Tools >>> Data Sources (ODBC)

Then click on System DSN, then the Add button, Select SQL-Server from the
list.

SQL-Server... If you say so, but there is an option for a microsoft
access driver... Isn't that the one I should choose?

 

[Paul Shapiro]

From searching on that error message number, it appears to be either a
permissions problem, or a sql statement problem. To check the sql statement,
open a new query, change to SQL view, and copy the entire sql statement into
the query. If the sql won't execute, that's the problem. If it does, I would
still guess it's a permissions issue.

Looking at the sql statement, maybe DSuspend and DTreatBusyAsWell are
boolean data types? If so, specifying a string value of 'off' might be the
problem. Try replacing 'off' with the number 0 (no quotes) and see if that
works.

You can get more details on the error that is occuring. Start by including
err.Description as well as err.Number. Here is a routine I used in asp to
get more info on errors. You can safely call this procedure using the
included sample call line anywhere in your code. If the error number is 0 it
doesn't do anything, but if there is an error it inserts details into the
page output. For now, try adding it after the line of code that is causing
your error.

Function pjsShowErrors( _
strSource, _
lngErrNumber, _
strErrDescription, _
strErrSource, _
oConn _
)
'Example: pjsShowErrors("Updating login date", err.number, err.description,
err.source, oConn)
on error resume next
Dim lngError, lngErrorCount, oErr, fSuccess, strMsg
Dim fErrorVBScript, fErrorDatabase

strMsg = ""
fErrorVBScript = True 'Safe default here
fErrorVBScript = Not (lngErrNumber = 0)
If fErrorVBScript Then
strMsg = strMsg & "Error #" & lngErrNumber & " in " & strSource & ".<BR>"
& vbCrLf
If Application("ErrorDetailDisplay") Then
strMsg = strMsg _
& " VBScript Error Detail: " & strErrDescription _
& " (in " & strErrSource & ")<br>" & vbcrlf
End If
err.clear
End If

If IsObject(oConn) Then
fErrorDatabase = False 'Default before testing
lngErrorCount = oConn.Errors.Count
If lngErrorCount > 0 Then
'See if there is an error or just an informational warning
If fErrorVBScript Then 'No need to check DB error status. Assume an
error.
fErrorDatabase = True
ElseIf Application("SQLServer") Then
'Positive error numbers are informational warnings in SQL Server?
For each oErr in oConn.Errors
If (oErr.number < 0) Then
fErrorDatabase = True
Exit For
End If
Next
Else
'Using MSAccess
'Zero error number seems to occur in Jet 4.0 database
' if it's been opened by MSAccess?
For each oErr in oConn.Errors
If (oErr.number <> 0) Then
fErrorDatabase = True
Exit For
End If
Next
End If

If fErrorDatabase Then
'Summary intro to the error list
strMsg = strMsg & lngErrorCount
If lngErrorCount = 1 Then
strMsg = strMsg & " error "
Else
strMsg = strMsg & " errors "
End If
strMsg = strMsg & " occurred in the Data Connection:<br>" & vbcrlf
If Application("ErrorDetailDisplay") Then
strMsg = strMsg _
& " Database Error Detail " & strSource _
& " (in " & strErrSource & ")<br>" & vbcrlf
End If

'Comprehensive error list
For each oErr in oConn.Errors
strMsg = strMsg & "Connection Error hex#" _
& hex(oErr.number) & ".<br>"
If Application("ErrorDetailDisplay") Then
strMsg = strMsg & " " & oErr.description
strMsg = strMsg & " (in " & oErr.source & ")<br>" & vbcrlf
strMsg = strMsg & " Native Error: " & oErr.nativeError & _
", SQL State: " & oErr.SQLState & "<BR>" & vbcrlf
End If
Next
End If
oConn.Errors.Clear
End If
End If

If len(strMsg)>0 Then
Response.Write strMsg
End If

fSuccess = Not (fErrorVBScript Or fErrorDatabase)
pjsShowErrors = fSuccess
end function

Get the complete asp error message and post it here.

You can check the file and folder security settings. The account under
which
the website is running needs Modify access to the folder and the db file
so
it can create the .ldb file when it opens the db. You can also look in the
Windows event log for detailed messages.

The asp file logs this:

INSERT INTO clientTable (SFirstName, SLastName, SAddress, SApt, SCity,
SStateProvince, SPostalCode, SCountry, STelephone, SEmailAddress,
DFirstName, DLastName, DAddress, DApt, DCity, DStateProvince,
DPostalCode, DCountry, DTelephone, DCallTime, DAmPm, DRetryInterval,
HName1, HTelephone1, HName2, HTelephone2, HName3, HTelephone3,
DSuspend, DTreatBusyAsWell) VALUES ('bull', 'shot', '1', '',
'calgary', 'ab', 'a2b2c3', 'canada', '4035551212', '(e-mail address removed)',
'calf', 'shot', '2', '', 'Calgary', 'ab', 'a2b2c3', 'Canada',
'4035551313', '1', 'AM', '15', 'ca1', '4035551414', 'ca2',
'4035551515', 'ca3', '4035551616','off','off')
Err = -2147217900

 

[Arvin Meyer [MVP]]

You do not need a driver for Access data and several others, only for ODBC
databases. Just link the data from the table on the server to the front-end
on the workstation. I find it easier to link a copy of the front-end
residing on the server, then copy to each workstation.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I wonder if you might be more specific about how to setup the database
in the Administrative Tools, Data Sources (ODBC) please.
-----------------------------------
Control Panel >>> Administrative Tools >>> Data Sources (ODBC)

Then click on System DSN, then the Add button, Select SQL-Server from the
list.

SQL-Server... If you say so, but there is an option for a microsoft
access driver... Isn't that the one I should choose?

 

[Arvin Meyer [MVP]]

Sorry, I was thinking you were using an Access front-end, not an asp one.
Yes, create the DSN using the Access ODBC driver.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I wonder if you might be more specific about how to setup the database
in the Administrative Tools, Data Sources (ODBC) please.
-----------------------------------
Control Panel >>> Administrative Tools >>> Data Sources (ODBC)

Then click on System DSN, then the Add button, Select SQL-Server from the
list.

SQL-Server... If you say so, but there is an option for a microsoft
access driver... Isn't that the one I should choose?

 

[Arvin Meyer [MVP]]

I set the permission from the root all the way to the mdb file to
Internet guest account, full control.
(This worries me because it sounds like a security risk, but I have to
get this going.)
Still no luck, and nothing in the event logs at all.
The exact error message is:
Err=-2147217900

===========

It is a security risk if you allow full control. Use read write only for
I_USER

I forget now what the exact permissions were, but we were able to block
copying for the entire data tables by limiting permissions on the MDB
filetype. In some respects an MDB is more secure because you cannot do SQL
injection on it. It only takes single SQL statements and requires parameters
when using code.

I'm not an asp guy, but I have written some and got it working successfully.
Most error messages which start with -214 are non-specific and tell you very
little.

 

[Arvin Meyer [MVP]]

For what it's worth, I am able to READ the database I just can't write
it.

So we know that the connection works. First check your permissions again,
remember I said that you need Read Write, not Full Control. But you don't
have a permissions problem if you are using full control.

Now try a simple INSERT statement. Inserting a single value into a copy of
the table which you will later delete. If you still get an error, it may be
a required field or incorrect datatype. Delete all the fields except 1
number and 1 text and try again. Keep adding back the fields from the "good
table" until you get it working. Now change the table name in your SQL
statement and buy yourself a beer. You've earned it.

 

[Hans Up]

I set the permission from the root all the way to the mdb file to
Internet guest account, full control.
(This worries me because it sounds like a security risk, but I have to
get this going.)

Do you have a need to allow your site visitors to download the entire
database (from a URL like http://yoursite/mydatabase.mdb)?

If not, place the database in a folder outside the webroot folder
hierarchy. Maybe C:\db\mydatabase.mdb Give the IIS user account the
necessary permissions there.

Then you can revert the permissions for C:\inetpub\wwwroot to eliminate
your security concern.

Your ASP code can still use the relocated database simply by changing
conn.Open to point to the revised path.

 

[SpreadTooThin]

Do you have a need to allow your site visitors to download the entire
database (from a URL likehttp://yoursite/mydatabase.mdb)?
No.

If not, place the database in a folder outside the webroot folder
hierarchy.  Maybe C:\db\mydatabase.mdb  Give the IIS user account the
necessary permissions there.

OK.

Then you can revert the permissions for C:\inetpub\wwwroot to eliminate
your security concern.