SQL works in access db but not in my adodb connection...why?

[xman51]

Hello all,

I am having an issue with an SQL statement. It keeps giving me a scripting error.

Here is the error output:

Table xman51 was found
INSERT INTO xman51 (URL,Title,Notes) VALUES("http://www.xarea51x.com","Area 51","The Official Area 51 band website.");
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

/Bisk-iNet/NewLink.asp, line 60

Here is my ASP Code:

<%
Dim URLID
Dim Title
Dim Notes
Dim UserN
Dim adoCon
Dim rsAddComments
Dim strSQL
Dim UserL
Dim DBName
Dim found

found = false
URLID = Request.Form("URL")
Title = Request.Form("Title")
Notes = Request.Form("notes")
UserN = Request.Cookies("username")
UserL = Request.Cookies("Level")
DBName1 = "Links.mdb"
'if there is no login information then redirect to login page
If UserN = "" Then
Response.redirect "Default.asp"
End if


GetTables_Server(DBName1)

'Create an ADO connection object
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(DBName1)

'if the table doesn't exist then create it
if found = false then

Set rsAddComments = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "CREATE TABLE " & UserN & " (URL char(200) null, Title char(200) null, Notes char(200) null);"
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the recordset with the SQL query
rsAddComments.Open strSQL, adoCon

rsAddComments.Close
Set rsAddComments = nothing

end if

Set rsAddComments = Server.CreateObject("ADODB.Recordset")
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "INSERT INTO " & UserN & " (URL,Title,Notes) VALUES(""" & URLID & """,""" & Title & """,""" & Notes & """);"
response.write strSQL
'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3
'Open the recordset with the SQL query
rsAddComments.Open strSQL, adoCon

rsAddComments.Close
Set rsAddComments = nothing
set adoCon = nothing

If UserL = "Admin" Then
Response.Redirect "Admin.asp"
ElseIf UserL = "WP" Then
Response.Redirect "WP.asp"
ElseIf UserL = "Editor" Then
Response.Redirect "Editor.asp"
End if



Sub GetTables_Server(DBName)
Dim i
Dim catDB 'ADODB.Catalog object

Set catDB = Server.CreateObject("ADOX.Catalog")
'Create ADODB.Catalog object

On Error Resume Next 'Error handle

catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath(DBName)
'Open connection
'Don't forget to add your user name and
'password if needed.
If Err.Number = 0 Then 'If no error occurred
' Produce dynamic SELECT menu

With catDB
For i = 0 To .Tables.Count -1
If .Tables(i).Type = "TABLE" then ' If is a table
if lcase(.Tables(i).Name) = lcase(UserN) then
response.write "Table " & .Tables(i).Name & " was found<BR>"
found = true
exit for
end if
End If
Next
End With

Else
'Error message
Response.Write "Error: Can't open database - " & DBName
End If
Set catDB = Nothing ' Free ADODB.catalog object
End Sub
%>

I don't know how to fix it. Anybody? Anybody?

Thanks in Advance,

[buntine]

The SQL query is expecting single quotes rather than escaped double quotes.

Use this:

strSQL = "INSERT INTO " & UserN & " (URL,Title,Notes) VALUES('" & URLID & "','" & Title & "','" & Notes & "');"

Regards.