VBScript Connection error to Access mdb.

**CMitch07** · 01-14-2008, 02:44 PM

Hi all,
I have set up my new site and hosted it on GoDaddy since it allows me to use an Access.mdb as a source for my VBScript ASP pages. I have created ASP pages with a SQL source in the past from a internal webserver but never Access in a Host companys site before. I am using Dreamweaver MX 2004 to create my ASP and at home, on the local pc my ASP page displays perfectly from the web browser however not so from the site itself. I have managed to resolve various different errors that have directed me to my connection coe but this last one has me stumped. The error is as follows: Microsoft VBScript compilation error '800a0401' Expected end of statement
And my code is below:
<%
' FileName="Connection_ado_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_access_MyDB_STRING
MM_access_MyDB_STRING = "Driver={Microsoft Access Driver(*.mdb)};DBQ=Server.MapPath("MyDB.mdb")"
%>
Can anyone assist with this problem ?
Thanks in advance,
Mitch.......

**chrishirst** · 01-14-2008, 03:21 PM

The quotes and concatenation is messed up

Code:

MM_access_MyDB_STRING = "Driver={Microsoft Access Driver(*.mdb)};DBQ=" & Server.MapPath("MyDB.mdb")

**CMitch07** · 10:46 PM

Thanks Chris but unfortunatly its not working, and I suspect GoDaddy is the cause !! I contacted support and they will not assist with 3rd party apps, and they sent me some example code to use, which worked, however trying to open that same code in Dreamweaver was fruitless. I don't understand why I can't develop my ASP pages in Dreamweaver for GoDaddy !
Below is an example of both codes in case somone can work out a way to mix the two successfully !
GoDaddy/Host:
<%
Dim oConn, oRs
Dim qry, connectstr, sDSNDir
Dim db_name, db_username, db_userpassword
Dim db_server, dsn_name
dsn_name = "access_MyData.dsn"
fieldname = "link"
tablename = "qrylinks"
sDSNDir = Server.MapPath("_dsn")
connectstr = "filedsn=" & sDSNDir & "/" & dsn_name
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT * FROM " & tablename
Set oRS = oConn.Execute(qry)
if not oRS.EOF then
while not oRS.EOF
response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & " "
oRS.movenext
wend
oRS.close
end if
Set oRs = nothing
Set oConn = nothing
%>

**CMitch07** · 01-14-2008, 10:47 PM

And the DreamWeaver code is as follows:
<%
Dim rstTest
Dim rstTest_numRows
Set rstTest = Server.CreateObject("ADODB.Recordset")
rstTest.ActiveConnection = MM_access_MyData_STRING
rstTest.Source = "SELECT * FROM qryLinks ORDER BY Link ASC"
rstTest.CursorType = 0
rstTest.CursorLocation = 2
rstTest.LockType = 1
rstTest.Open()
rstTest_numRows = 0
%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<table width="800" border="0">
<tr align="left" valign="top">
<th width="99" height="349" scope="col"><P> <%=(rstTest.Fields.Item("Link").Value)%> </P></th>
<th width="354" scope="col"> </th>
<th width="333" scope="col"> </th>
</tr>
</table>
</body>
</html>
<%
rstTest.Close()
Set rstTest = Nothing
%>

**sri_gan** · 01-16-2008, 10:26 AM

CMitch,

Use the below code. I merged that for you, Make sure the access database is in the accessible path. Have fun!

Code:

<%
Dim rstTest,oConn
Dim rstTest_numRows, MM_access_MyDB_STRING

MM_access_MyDB_STRING = "Driver={Microsoft Access Driver(*.mdb)};DBQ=" & Server.MapPath("MyDB.mdb")

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open MM_access_MyData_STRING
Set rstTest = Server.CreateObject("ADODB.Recordset")
rstTest.ActiveConnection = oConn
rstTest.Source = "SELECT * FROM qryLinks ORDER BY Link ASC"
rstTest.CursorType = 0
rstTest.CursorLocation = 2
rstTest.LockType = 1
rstTest.Open()
rstTest_numRows = 0
%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<table width="800" border="0">
<tr align="left" valign="top">
<th width="99" height="349" scope="col"><P> <%=(rstTest.Fields.Item("Link").Value)%> </P></th>
<th width="354" scope="col">&nbsp;</th>
<th width="333" scope="col">&nbsp;</th>
</tr>
</table>
</body>
</html>
<%
rstTest.Close()
oConn.Close()
Set rstTest = Nothing
%>

**ADAM Web Design** · 01-16-2008, 11:21 AM

The reason is because GoDaddy created what's known as a DSN connection, which means that they had to set up a System DSN on their server to make it work. This is more resource-intensive and requires more configuration than your code, which should work as long as you use Hirst's modification and do what sri_gan said (i.e. make sure your database is in the same folder as your code).

GoDaddy is right not to support third-party scripts. Almost no host does, unless it's something they put on there themselves. There would simply be too much time wasted as crappy developers put up crappy code and hosts have to go fixing it.

Of course, all of this is a moot point if we don't understand what the error is. If you can get the error code, that would go a long way toward telling us more.

**CMitch07** · 01-16-2008, 11:55 AM

Thank you Sri Gan. I'll give that a go today !

Mitch....