Posting records from Unbound Form

Question

Posting records from Unbound Form

Steven Deetz

SSC Veteran

Points: 224
More actions
December 12, 2006 at 3:34 pm

#67821

The strangest thing is occuring with an Access 2003 FE and a SQL Server 2000. When I create new ADO methods of Add New/Update the code will run without any error messages but the records will not post to the SQL Server 2000 tables. My "older" ADO methods of Add New/Update work just fine. Both old and new use and re-use a common connection.
It has been suggested that I need to specify more parameters so SQL Server errors will show in Access but I am at a loss for these parameters. Below is a sample of the code that does not error out but does not post the record.
Thanks in advance for any suggestions!
--------------------------------------------
fOK = OpenConnection()
If Not fOK Then
Err.Raise 99999
End If
Set rstADO = New ADODB.Recordset
strSQLADO = "SELECT * FROM tbl_ReoccurringPayments" _
& "WHERE MedichargeSubscriberID = " & "'" & strSubID & "'"
rstADO.Open strSQLADO, cnnlocal, adOpenDynamic, adLockBatchOptimistic
rstADO.AddNew
rstADO!MediChargeSubscriberID = strSubID
rstADO!MedicalCategory = strProcCat
rstADO!InsurancePayment = ckIns
rstADO!DayDue = intDue
rstADO!Amount = curAmt
rstADO!FeeAmount = curFee
rstADO!MediChargeProviderID = strProvID
rstADO!SpecialNotes = strNotes
rstADO!CPNY = strEmp
rstADO.Update
rstADO.Requery
rstADO.Close
Set rstADO = Nothing

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Michael Meierruth SSChampion Points: 10051 More actions · Answer 1

I think you need to remove the rstADO.Requery line. I have never used it in this context and reading up on it quickly seems to pretty much convince me that it has no place here.

Steven Deetz SSC Veteran Points: 224 More actions · Answer 2

I removed the rstADO.Requery and ran the procedure. Same outcome where the code runs fine but nothing is posted to the table.

Thanks for the idea.

kevin mann Hall of Fame Points: 3894 More actions · Answer 3

Do yourself a service and use a stored proc and adodb.command object to insert records.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 4

What's fOK and cnnlocal? Which of them are the ADO connection object?

Steven Deetz SSC Veteran Points: 224 More actions · Answer 5

fOK is a boolean variable on the form and it equals the boolean outcome of the Public Function OpenConnection(). cnnlocal is the ADO Connection object. It is declared as a Public Variable and instantiated in the OpenConnection() function. The connection string is:

Provider=SQLOLEDB.1;Date Source=(local);Initial Catalog="MedichargeTables1";Integrated Security=SSPI

The connection is shared by other pieces of the application to pull information into Access and to push updated or new information back up into SQL Server.

What is so frustrating is that with no errors being brought up during the code execution I cannot say if the problem is a connection issue or not.

Steven Deetz SSC Veteran Points: 224 More actions · Answer 6

Kevin,

As a SQL Sever newbie I think I understand what you are saying. If I wanted to use a stored proc I would have to pass up the data in the form fields as variables to the stored procedure for appending to an SQL Server table.

In other parts of my application I have multiple records that I either want to append or update in a SQL Server table. How would you send multiple records into a stored proc for updating?

Thanks for putting up with my newbie questions.

kevin mann Hall of Fame Points: 3894 More actions · Answer 7

I am unsure if this will answer your Question, but this is my usual insert/update approach for "unbinding" forms.

CREATE PROCEDURE sp_Table1UPD

@tableID bigint = null OUTPUT,

@value1 varchar(50)

AS

IF @tableID IS NULL --INSERT

BEGIN

INSERT INTO Table1

(value1)

SELECT @value1

SET @tableID = SCOPE_IDENTITY() --return the ID back to the comman object

END

ELSE --UPDATE

BEGIN

UPDATE Table1

SET value1 = value1

WHERE tableID = @tableID

END

Scott Reynolds-389385 Newbie Points: 3 More actions · Answer 8

Try changing rstADO.Update to rstADO.UpdateBatch.

Also, you don't need rstADO.Requery.

Hope this helps. 😎

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 9

Steve,

I'm not too familiar with ADO but the following code worked for me:

Private Sub Command0_Click()

Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=.\ADODATA.mdb;"

Set rstADO = New ADODB.Recordset

rstADO.Open "SELECT * FROM subs WHERE subID = '" & Me![SUBID] & "'", cnn, adOpenDynaset, adLockBatchOptimistic

rstADO.AddNew

rstADO!SUBID = Me!SUBID

rstADO!Subname = Me!Subname

rstADO.UpdateBatch

rstADO.Requery

rstADO.Close

Set rstADO = Nothing

End Sub

Changes made were OpenDynaset and also me! to point where variables are coming from - ie Form

Richie

Steven Deetz SSC Veteran Points: 224 More actions · Answer 10

Scott,

Update Batch appears to do the trick!

I also compared the table's properties with another table that I can post to and found the table in question not clustered. By changing both settings it appears to work!

Rich,

Thanks for the ADO code on what to work but your connection string is for Jet and not for SQL Server. I don't think I would get too far with that.

Kevin,

Thanks for the code sample for a stored procedure. I am going to play with that and see if I can get that to work!

Thanks everyone for your help!

Bill Holt Mr or Mrs. 500 Points: 527 More actions · Answer 11

Steven,

As to why you didn't receive an error message...

We have one installation of Access 2003 that will not return error messages unless that user compiles the MDB on their own machine. Weird problem but it happens.

Bill