trouble adding records to table

[Keeage]

From: Keeage - view profile
Date: Sat, Dec 30 2006 7:02 pm
Email: "Keeage" <[email protected]>
Groups: microsoft.public.access.formscoding
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

I'm making a database that inputs large text files and stores the data.
I can connect to the db, display it's contents and navigate, but when
I try to add records, everything appears to function properly, but
there are no new records in the table. Could someone tell me what I'm
doing wrong?

This is the code I've written:

'define variables
lgNeighCode = 5433
stAddress = "Road to nowhere"
stTransfer = "Me to You"
intClosedPrice = 5487

Dim sql As String
Dim rsAdd As New ADODB.Recordset

On Error GoTo DbError

'Open the Recordset object
rsAdd.Open "Temp", localConnection, adOpenDynamic, _
adLockBatchOptimistic, adCmdTable

'add the record based on input from the user
'gonna use stored variables
With rsAdd
.AddNew
!NeighCode = lgNeighCode
![Full Address] = stAddress
!Transfer = stTransfer
!Closed = intClosedPrice
.Update
.Close
End With

MsgBox "Record Should be added.", vbInformation

'close form-level recordset object and refresh
'it to include the newly updated row.

rsTransfers.Close
connectDB

Exit Sub

I've been stuck at this point for several days and I would greatly
appreciate any help.

Thanks

Keeage

 

[SteveS]

Keeage,

I haven't used ADO yet in my databases, so this was a learning experience
for me too. From what I read, it looks like you have several syntax errors in
your code.

Try the following

Warnings: Watch for line wrap/ AIR CODE!

'-------BEG CODE---------------
On Error GoTo ErrorHandler

Dim rsAdd As New ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strSQL As String

' the following variables are only for testing purposes
'when the new record is added,
' controls on a form should be referenced or
' this should be a Function with the data passed
' as arguments with this code in the Click event of a button.
' (how I would set it up)

'-----define test variables----
Dim lgNeighCode As Long
Dim stAddress As String
Dim stTransfer As String
Dim intClosedPrice As Integer

'assign values to test variables
lgNeighCode = 5433
stAddress = "Road to nowhere"
stTransfer = "Me to You"
intClosedPrice = 5487
'-------------------------------------


'set the ado connection
Set Cnxn = New ADODB.Connection
Set Cnxn = CurrentProject.Connection

'set the table name
strSQL = "Temp"

'open the ado recordset
rsAdd.Open strSQL, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable

'add the record based on input from the user
'gonna use stored variables
With rsAdd
!NeighCode = lgNeighCode
![Full Address] = stAddress
!Transfer = stTransfer
!Closed = intClosedPrice
.Update
End With

' Show the newly added data
MsgBox "New record: " & rsAdd!lgNeighCode & _
", " & rsAdd!stAddress & _
", " & rsAdd!stTransfer & ", " & rsAdd!intClosedPrice

' MsgBox "Record Should be added.", vbInformation

'clean up
rsAdd.Close ' close recordset
Cnxn.Close ' close connection

'destroy objects
Set rsAdd = Nothing
Set Cnxn = Nothing

Exit Sub

ErrorHandler:
' clean up
If Not rsAdd Is Nothing Then
If rsAdd.State = adStateOpen Then rsAdd.Close
End If
Set rsAdd = Nothing

If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'-------END CODE---------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

From: Keeage - view profile
Date: Sat, Dec 30 2006 7:02 pm
Email: "Keeage" <[email protected]>
Groups: microsoft.public.access.formscoding
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

I'm making a database that inputs large text files and stores the data.
I can connect to the db, display it's contents and navigate, but when
I try to add records, everything appears to function properly, but
there are no new records in the table. Could someone tell me what I'm
doing wrong?

This is the code I've written:

'define variables
lgNeighCode = 5433
stAddress = "Road to nowhere"
stTransfer = "Me to You"
intClosedPrice = 5487

Dim sql As String
Dim rsAdd As New ADODB.Recordset

On Error GoTo DbError

'Open the Recordset object
rsAdd.Open "Temp", localConnection, adOpenDynamic, _
adLockBatchOptimistic, adCmdTable

'add the record based on input from the user
'gonna use stored variables
With rsAdd
.AddNew
!NeighCode = lgNeighCode
![Full Address] = stAddress
!Transfer = stTransfer
!Closed = intClosedPrice
.Update
.Close
End With

MsgBox "Record Should be added.", vbInformation

'close form-level recordset object and refresh
'it to include the newly updated row.

rsTransfers.Close
connectDB

Exit Sub

I've been stuck at this point for several days and I would greatly
appreciate any help.

Thanks

Keeage

 

[RoyVidar]

From: Keeage - view profile
Date: Sat, Dec 30 2006 7:02 pm
Email: "Keeage" <[email protected]>
Groups: microsoft.public.access.formscoding
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

I'm making a database that inputs large text files and stores the
data. I can connect to the db, display it's contents and navigate,
but when I try to add records, everything appears to function
properly, but there are no new records in the table. Could someone
tell me what I'm doing wrong?

This is the code I've written:

'define variables
lgNeighCode = 5433
stAddress = "Road to nowhere"
stTransfer = "Me to You"
intClosedPrice = 5487

Dim sql As String
Dim rsAdd As New ADODB.Recordset

On Error GoTo DbError

'Open the Recordset object
rsAdd.Open "Temp", localConnection, adOpenDynamic, _
adLockBatchOptimistic, adCmdTable

'add the record based on input from the user
'gonna use stored variables
With rsAdd
.AddNew
!NeighCode = lgNeighCode
![Full Address] = stAddress
!Transfer = stTransfer
!Closed = intClosedPrice
.Update
.Close
End With

MsgBox "Record Should be added.", vbInformation

'close form-level recordset object and refresh
'it to include the newly updated row.

rsTransfers.Close
connectDB

Exit Sub

I've been stuck at this point for several days and I would greatly
appreciate any help.

Thanks

Keeage

Switch .Locktype from adLockBatchOptimistic to adLockOptimistic

(you won't get dynamic cursor on Jet tables, btw)

But - you don't want to use recordset approaches for this, you'd
want to use action queries (air code).


dim strSql as string
strsql = "INSERT INTO Temp " & _
"(NeighCode, [Full Address], Transfer Closed )" & _
"Values (" & lgNeighCode & ", '" & stAddress & "', '" & _
stTransfer & "', " & intClosedPrice & ")"
localConnection.Execute strSql, , adcmdtext+adexecutenorecords

 

[Keeage]

IT WORKS!!!

Thank you, your code worked great.

I need to do a bit more reading on sql, you know I used code from a
book on Access 2003 to write mine. Think they should have known that
about the Jet tables.

Thanks again, I was stuck there for the last several days.

Keeage

From: Keeage - view profile
Date: Sat, Dec 30 2006 7:02 pm
Email: "Keeage" <[email protected]>
Groups: microsoft.public.access.formscoding
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author

I'm making a database that inputs large text files and stores the
data. I can connect to the db, display it's contents and navigate,
but when I try to add records, everything appears to function
properly, but there are no new records in the table. Could someone
tell me what I'm doing wrong?

This is the code I've written:

'define variables
lgNeighCode = 5433
stAddress = "Road to nowhere"
stTransfer = "Me to You"
intClosedPrice = 5487

Dim sql As String
Dim rsAdd As New ADODB.Recordset

On Error GoTo DbError

'Open the Recordset object
rsAdd.Open "Temp", localConnection, adOpenDynamic, _
adLockBatchOptimistic, adCmdTable

'add the record based on input from the user
'gonna use stored variables
With rsAdd
.AddNew
!NeighCode = lgNeighCode
![Full Address] = stAddress
!Transfer = stTransfer
!Closed = intClosedPrice
.Update
.Close
End With

MsgBox "Record Should be added.", vbInformation

'close form-level recordset object and refresh
'it to include the newly updated row.

rsTransfers.Close
connectDB

Exit Sub

I've been stuck at this point for several days and I would greatly
appreciate any help.

Thanks

Keeage

Switch .Locktype from adLockBatchOptimistic to adLockOptimistic

(you won't get dynamic cursor on Jet tables, btw)

But - you don't want to use recordset approaches for this, you'd
want to use action queries (air code).


dim strSql as string
strsql = "INSERT INTO Temp " & _
"(NeighCode, [Full Address], Transfer Closed )" & _
"Values (" & lgNeighCode & ", '" & stAddress & "', '" & _
stTransfer & "', " & intClosedPrice & ")"
localConnection.Execute strSql, , adcmdtext+adexecutenorecords