What's wrong with the code?

[Jack]

Hi,
I have access as front end and sql server as backend. I am trying to add a
record from a AddLineItem form on to sql server. The following is the code I
have written. However, for some reason the code does not work. The error
message I got here is that variable not defined. This happens at the line Set
conn = Server.CreateObject("ADODB.Connection"). I would appreciate any help
on this. THanks
CODE:
Dim strSql As String
Dim conn As String
Dim rs As Recordset

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "Provider=MSDASQL.1;Password=abcd;Persist Security Info=True;User
ID=sa;Data Source=ADH2KSQL"

'create recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

'create SQL string for inserting data into database

strSql = "SELECT [tblLineItems].[LineItemID], [tblLineItems].[Description]
FROM tblLineItems"
'set lock type to adLockOptimistic so that we can add the new record
'open the recordset
rs.LockType = 3 '
rs.Open strSql, conn

'now add data with AddNew Method
rs.AddNew
rs("LineItemID") = LineItemID
rs("Description") = Description


'update the record
rs.Update
MsgBox ("Record successfully update")

'close the recordset and destroy from memory
rs.Close
Set rs = Nothing

'close connection object
conn.Close
Set conn = Nothing

DoCmd.Close
Forms("frmLineItems").Visible = True

 

[Jeff Boyce]

Jack

If you are using the

Set conn = ...

Access is looking for an object. You Dim'd conn as a string, not a
connection object.

Regards

Jeff Boyce
Microsoft Office/Access MVP