Connecting to Access Db

mattastic · October 13th, 2004, 05:31 AM

Hi,

I'm using the following code to try and connect to a simple access db, but i keep getting an error.

The weird thing is it works fine on my test server , but when i upload to my webspace it just wont work. I've double checked the connection string too.

I've included the error and the code below, hope you can help.

error

Code:

Microsoft JET Database Engine error '80040e14' 

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. 

/testasp.asp, line 19

Page code

Code:

<%Option Explicit%>
<HTML>
<HEAD>
<TITLE>Testing our connection</TITLE>
</HEAD>
<BODY>

<%
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=d:/webspace/w00t.biz/db/newsletter.mdb;" & _
               "Persist Security Info=False"



objRS.Open "newsletter", objConn

While Not objRS.EOF
  Response.Write objRS("email") & "<BR>"
  objRS.MoveNext
Wend

objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing 
%>

</BODY>
</HTML>

om_prakash · October 13th, 2004, 07:47 PM

instead of

objRS.Open "newsletter", objConn

try adding

objRS.Open "select * from newsletter", objConn

Note:

When opening a recordset you can pass an optional CommandTypeEnum value
(eg adCmdText, adCmdTable or adCmdStoredProc) to ADO to help it identify the source of your data.

If you choose to specify a database table as your data source, but pass the CommandTypeEnum value for adCmdText,
then you will receive the following error message. Either change the CommandTypeEnum to adCmdTable or adCmdTableDirect -or-
use an SQL query as the source of your recordset.

Om Prakash

rajanikrishna · October 13th, 2004, 09:15 PM

hi mattastic,

Om prakash is right. U try that way.
And i would like to suggest one more thing. Why are you using physical path in the connection string?

It will not work outside your LAN.

Instead, use server.mappath(dbname)

----------
Rajani

mattastic · October 14th, 2004, 02:32 AM

Thanks for your help