Execute SQL

[Earl Partridge]

Access 2000, Windows XP

I'm using what I believe to be a DSNLess connection with:
cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & Server.MapPath("/db/mydatabase.mdb") & ";"
set conn = CreateObject("ADODB.Connection")
conn.open cst

And attempting to insert a new record with the sql statement:

Insert into MyComments (Password, EntryDate, Comments) Values ('MyPW', #3/19/09# , 'test')

Conn.execute sql

This is being done live at my host. I get a Syntax Error.
Microsoft JET Database Engine error '80040e14'

At first I thought my date field was the problem, changed that to a text field, etc. I use this
routine in other databases with no problem. I copied & pasted this sql statement as a query
on my local machine and it was accepted.

Perhaps this particular database at my host's site won't accept DSNLess?

Is there another insert method other than "Conn.execute sql"?

Earl

 

[Norman Yuan]

You did not say, but it seems you are talking a web application. Since you have verified that the SQL Statement works on your local machine, it is, then, very likely that the user account in your host server that runs your web application (ASP?) does not have required permission to use *.mdb file (write and create permission to the folder where the *.mdb file is). So, you need to verify with your ISP to configure the web server (IIS?) correctly so that the account running the web app has proper permission.

BTW, using jet database (*.mdb) is hardly a good choice for a web application, especially if it is not a intranet app.

Access 2000, Windows XP

I'm using what I believe to be a DSNLess connection with:
cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
cst = cst & Server.MapPath("/db/mydatabase.mdb") & ";"
set conn = CreateObject("ADODB.Connection")
conn.open cst

And attempting to insert a new record with the sql statement:

Insert into MyComments (Password, EntryDate, Comments) Values ('MyPW', #3/19/09# , 'test')

Conn.execute sql

This is being done live at my host. I get a Syntax Error.
Microsoft JET Database Engine error '80040e14'

At first I thought my date field was the problem, changed that to a text field, etc. I use this
routine in other databases with no problem. I copied & pasted this sql statement as a query
on my local machine and it was accepted.

Perhaps this particular database at my host's site won't accept DSNLess?

Is there another insert method other than "Conn.execute sql"?

Earl