Insert into Access database through ADO

[cpaige]

Hey,
I'm developing a VB Application that accesses Access 2000 through ADO. I have been trying to insert data into a few tables and it gived me the same error.

Here is the entire function that returns the error.

Public Function InsertAddress(iStreetNameID As Long, sStreetNumber As String, sPostalCode As String, sUnitNo As String, sCity As String, sCountry As String, iDepartment As Long) As Long
Dim sSQL As String
Dim rsTemp As ADODB.Recordset

InsertAddress = -1
sSQL = "INSERT INTO Address (StreetNameID, StreetNumber, PostalCode, UnitNo, City,Country,DepartmentID) " & _
"VALUES (%sni, '%sn', '%pc', '%un', '%ci','%co', %dep);"
sSQL = Replace(sSQL, "%sni", iStreetNameID)
sSQL = Replace(sSQL, "%sn", sStreetNumber)
sSQL = Replace(sSQL, "%pc", sPostalCode)
sSQL = Replace(sSQL, "%un", sUnitNo)
sSQL = Replace(sSQL, "%ci", sCity)
sSQL = Replace(sSQL, "%co", sCountry)
sSQL = Replace(sSQL, "%dep", iDepartment)

GetADOConnection().Execute sSQL

sSQL = "SELECT MAX(AddressID) FROM Address ORDER BY AddressID DESC"
InsertAddress = GetADORecordset(sSQL)(0)
End Function

When I run this function get the error:
Unknown Function , -2147467259

It's very strange. I get this with other INSERT STATMENT. What could it be?

Thanks!

Clayton T. Paige
claytonpaige@yahoo.ca

Programmer Extraordinaire

========================================================

"Who is General Failure? and Why is he reading my disk drive?"

 

[davidrobin]

The only thing I can notice without running it is the line

InsertAddress = GetADORecordset(sSQL)(0)

I cannot see what the (0) is doing on the end. David
Visual Basic 6 Ent

 

[cpaige]

GetADORecordset is a function that I created that returns a recordset. Since it returns a recordset , you can place "(0)" after it and it will return the first value of the first field for the SQL statement requested in that recordset. This is handy when you just want one value and you are sure there will be at least one returned. It eliminates the need for instantiating a local adobb.recordset object.

InsertAddress = GetADORecordset(sSQL)(0)

Clayton T. Paige
claytonpaige@yahoo.ca

Programmer Extraordinaire

========================================================

"Who is General Failure? and Why is he reading my disk drive?"

 

[cpaige]

I figured it out. I found some guys solution to the problem on the web. It seems that Jet does support using ENVIRON("USERNAME&quot as being a default value to a field. NOW() seems to work though. I'm just glade I found the problem. I guess I just have to assign the value in the SQL.

Top of the day to you all.
Clayton T. Paige
claytonpaige@yahoo.ca

Programmer Extraordinaire

========================================================

"Who is General Failure? and Why is he reading my disk drive?"