4/25/2024 3:29:38 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
Access 2000 - insert using OLEDB connection?
Posted: 18 Nov 09 12:54 PM
|
Hello,
Does anyone have a working mod to insert a record from MS Access 2000 via SLX OLEBD for an onclick event? We cannot get the correct syntax to compile even a very simple script. Not sure if we are missing references?
The Access project is connected to SLX(7.2) database (SQL 2005) for this specific event we need to use the SLX provider to insert a record (to auto increment table ID). Thought it would be simple (we do inserts all the time) but... 
thanks!
j |
|
|
|
Re: Access 2000 - insert using OLEDB connection?
Posted: 19 Nov 09 3:07 AM
|
What is the error message that you are receiving?
Are you writing a SECCODEID value with the record (This is a required field, and will generate an Error if not populated).
Did you turn the AUTOINCREMENT for the Contact table on the CONTACTID field (this is not done out of the box, you need to do it yourself).
As far as writing data with AUTOINCREMENT turned on:
Dim aRec
Set aRec = CreateObject("ADODB.Recordset")
aRec.CursorType = 3
aRec.CursorLocation = 3
aRec.LockType = 3
'I am assuming that aCon is a connection object initialized with an SLXOLEDB connection string and that has already been opened.
aRec.Open "SELECT * FROM CONTACT WHERE 1 = 2", aCon
aRec.AddNew
aRec("SECCODEID") = "SYST00000001" 'defaulting to Everyone.
aRec("FIRSTNAME") = "Jon"
aRec("LASTNAME") = "Doe"
aRec.Update
Set aRec = Nothing
|
|
|
|
Re: Access 2000 - insert using OLEDB connection?
Posted: 19 Nov 09 12:21 PM
|
Hi Raul!
Thanks for getting back to us. We are updating the History table only. Does SECCODEID need to be defined even if it isn't a field in that table? Autoincrement is enabled. We are getting an out of memory message after major hang time. The code compiles in ACCESS without an error. Test code below.
Private Sub Command5_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=SLXOLEDB.1 assword=ersist Security Info=True;User ID=;Initial Catalog=Test_Database;Data Source=SLX;Extended Properties=PORT=1706;LOG=ON;"
Set rs = New ADODB.Recordset
rs.CursorType = 3
rs.CursorLocation = 3
rs.LockType = 3
rs.Open "HISTORY", cn
rs.AddNew
rs("TYPE") = 262148
rs("ACCOUNTID") = "A6EKNA000GQZ"
rs("CONTACTID") = "C6EKNA00146K"
rs("ACCOUNTNAME") = "Test Account"
rs("CONTACTNAME") = "Name"
rs("CATEGORY") = "Collections"
rs("STARTDATE") = Now()
rs("DURATION") = 0
rs("DESCRIPTION") = "Verify Sent"
rs("USERID") = "U6EKNA000023"
rs("USERNAME") = "Name"
rs("ORIGINALDATE") = Now()
rs("RESULT") = "Complete"
rs("CREATEDATE") = Now()
rs("CREATEUSER") = "U6EKNA000023"
rs("MODIFYDATE") = Now()
rs("MODIFYUSER") = "U6EKNA000023"
rs("NOTES") = "List sent to Customer"
rs("LONGNOTES") = "List sent to Customer - Long Long Long Long Long Long Long Long Notes"
rs.Update
rs.Close 'Close record
cn.Close 'Close connection
End Sub |
|
|
|
Re: Access 2000 - insert using OLEDB connection?
Posted: 19 Nov 09 12:38 PM
|
Once I looked at your code, the problem is quite obvious (Keep reading for Solution).
I am not sure where I got CONTACT out of, shouldn't reply that early in the AM. But if you notice on my example, when I opened the recordset I used a well known trick to avoid the exact problem you are having.
The problem does not lie with SLX, the OLEDB Provider or the Auto Increment.
What is happenning is that you are attempting to load the whole History table into a Client Side recordset, thus the Out of Memory error after a while.
rs.Open "History", cn <<< This is a NO NO for any application, specially if using Client Site Recordsets. You are telling it to retreive the full history table.
Instead, follow my example and open an empty recordset:
rs.Open "SELECT * FROM HISTORY WHERE 1 = 2", cn
|
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|