Updating an Access database

[capone67]

Hi Gang

I am using the code below to try and update an access database in my VB code. I used the same code on another project and was able to make changes to the database. The database I am using is access 2000. I am also having problems with my ASP connecting to my databases now that I have converted the database from access 97 to 2000. I am wondering if my system is having issues of some kind. The error message that I receive from my code is run time error 3219.

Any help would be greatly appreciated.

Ken

PS I have included my code and the error message I get from my ASP.

'set database information for recording that a coupon has been printed
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbCouponworx = wrkJet.OpenDatabase("c:\inetpub\

http://wwwroot\coupons\db\report"

& CompanyNumber & ".mdb&quot

'get coupon information from the database
MySQL = "Insert into CouponReport (KioskID, MemberID, CouponID, PrintTime) Select " _
& "'" & KioskID & "', '" & MemberID & "', " & CouponIDs(Index) & ", #" & Now & "#;"

Set rsCoupons = dbCouponworx.OpenRecordset(MySQL, dbOpenDynaset, dbOpenDynamic)






Here is the error message I get from my ASP

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x4d4 Thread 0x5a0 DBC 0x2af2574 Jet'.
/coupons/template1.asp, line 7

 

[ricrecar]

Right off the bat I would recommend you start using ADO instead of DAO.

Here is some sample code.

'Here is the connection string to the DB:

gstrConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Password=; Data Source=c:\inetpub\

http://wwwroot\coupons\db\report"

& CompanyNumber & ".mdb; Persist Security Info=True"


'Here is a generic execute function

'Requires a valid sql statement
Public Function ExecuteSQL(ByVal sSQL As String) As Boolean
Dim objConn As ADODB.Connection
On Error GoTo ErrorHandler

Set objConn = CreateObject("ADODB.Connection&quot
objConn.Open gstrConnect
objConn.Execute sSQL, , adExecuteNoRecords
ExecuteSQL = True

Exit Function
ErrorHandler:
...handle the errors...
End Function

 

[capone67]

I have a feeling the problem is with my system rather than the code. I'll get back to you if I still have problems.

Ken

 

[PCSAARON]

This may be due to the fact of how Mysql treats data. I definitely agree with RICRECAR about using ADO. If you are trying to put a string into an integer this error message will come up as well. Check your data types. This is a little late, but hope it will help other people in the future.

Aaron

 

[PCSAARON]

Also, Error 3219 will appear if your using ADO and you don't do a .Update before .close.

Aarno

 

[petermeachem]

MySQL = "Insert into CouponReport (KioskID, MemberID, CouponID, PrintTime) Select " _
& "'" & KioskID & "', '" & MemberID & "', " & CouponIDs(Index) & ", #" & Now & "#;"

This line is wrong. You don't need a SELECT, you need a VALUES ( and a close btacket at the end. Also, are KioskID and MemberID strings? If they are integers drop the '

From help
Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...]) Peter Meachem
peter @ accuflight.com