Problem with Yes/No DataType. Also, Split a DB. How?

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

Here’s the code:
Private Sub Post_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\Pivot Trading System.accdb"
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mydb
cnn1.Open strCnn
' Open contact table.
Set rstcontact = New ADODB.Recordset
rstcontact.CursorType = adOpenKeyset
rstcontact.LockType = adLockOptimistic
rstcontact.Open "Options", cnn1, , , adCmdTable
'get the new record data
rstcontact.AddNew
rstcontact!OptionNo = OptionNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close
cnn1.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

When I run the code, I get a run-time error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.

First Question:
In my Options table, ‘Approved’ is Data Type Yes/No. On the Form, I have a
CheckBox named ‘Approved’. How do I convert this value on the Form to a
DataType that the Table will recognize?

Second Question:
Also, the DB is working from my C-drive now, as evidenced here:
mydb = "C:\Pivot Trading System.accdb"

This DB will soon go on a network. Will I split the DB, as discussed in
this link?
http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18


Then, have each front-end on the C-drive and still use that line of code:
mydb = "C:\Pivot Trading System.accdb"

I guess I don’t understand how Access will ‘know’ whether to go to the
network-drive or the C-drive. I’ve never done this before so I’m looking for
some advice as to how to proceed.

Thanks!
Ryan--
 
First and foremost - please only post one issue at a time. Some issues can
turn into rather lengthy discussions and having only one subject per post
helps you out in the long run.

I've never worked with .ACCDB files, however its my understanding that when
it comes to accessing the data contained therein, that its pretty much the
same as a .MDB file. Based on the code and comments further down, you should
not have to open an explicit connection to table that you're trying to
access. Simply put, if you see the table under the 'TABLES' group in your
front end, the connection (for all practical purposes) already exists. Its
just a matter of creating a recordset object that points to the data. I made
notes in the code below that should allow you to add a new record using DAO.
Originally ADO (within Access) was intended to replace DAO, however its held
on and should be around for a while.

To answer the second questions, a split database is just that split. The
underlying tables reside in one file, the objects to work with the data
reside in another. The front end is linked to the backend by creating links
to the table (GET EXTERNAL DATA >> LINK TABLES). You'll have to look for the
similar functionality in Access 2007, but it is there. Once the tables are
linked, they're linked. The front end can be located on any drive, in any
directory, and it will still point to the back end. You can even move it
around to hearts desire, make multiple copies each in a different location
on a machine or on multiple machines and they will all point back to the
backend.

When you link a table, Access captures the path to the backend and saves it.
Thus as long as the backend remains the same, the location of the front end
is pretty much irrelevant. Think of it as making a phone call - once you
have the telephone number, it doesn't make any difference whatsoever where
you are when you dial the number as the call will go through.

ryguy7272 said:
Here's the code:
Private Sub Post_Click()
Dim err As Integer
Click to expand...

'Delete its not neccessary
Dim cnn1 As ADO.Connection

'Change to DAO
Dim rstcontact As DAO.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then
Click to expand...

'DAO -----------------------------------------------------------------------------------------------

strSQL = [INSERT SELECT STATEMENT HERE]
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


'------------------------------------------------------------------------------------------------------
'get the new record data
rstcontact.AddNew
rstcontact!OptionNo = OptionNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close
'DELETE


Else
MsgBox "An Error has occurred, please check and try again"
End If
Click to expand...

Add explict SET statements here to destroy your objects as in

SET rs = NOTHING
SET db = NOTHING

Close what you open, destroy what you create.
 
And on a side note, when you're adding data, if you're entering the data via
a form that is bound to a record set (the form's recordset property is set
to a table or SQL statement), Access will handle adding the data. Its just a
matter of creating controls that are bound to their respective fields,
entering the data and then saving it by one of the many actions that will
cause the data to be comitted (shift+enter, save record on the
toolbar/ribbon, navigating to a previous record, moving to a new record,
etc.)
 
Thanks for the help David! I’m definitely missing something here!! What is
this for:
strSQL = [INSERT SELECT STATEMENT HERE]

I don’t understand that. I want to make this as ‘dynamic’ as possible. In
terms of that goal, this line seems to be appropriate:
Set db = CurrentDb

Still something wrong. Here is the code that I’m working with now:
Private Sub Post_Click()
Dim err As Integer
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then

mydb = "C:\Pivot Trading System.accdb"
strSQL = mydb
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

'get the new record data
rstcontact.AddNew
'rstcontact!OptionsNo = OptionsNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

Set rs = Nothing
Set db = Nothing

End Sub

What am I missing? Also, as I stated initially, I don’t know how to handle
one thing in the code. In my Options table, ‘Approved’ is Data Type Yes/No.
On the Form, I have a CheckBox named ‘Approved’.
rstcontact!Approved = Approved

How do I convert this value on the Form to a DataType that the Table will
recognize? Maybe it requires an If…Then?

Thanks again for the help!
Ryan--
 
The variable strSQL is there to hold the select statement as in SELECT
fieldName1, fieldName2, fieldName3 FROM tableName.

But keep in mind my second post, if you're working with a form on which the
data is entered, the code becomes a moot point as Access can handle adding
the record.

ryguy7272 said:
Thanks for the help David! I'm definitely missing something here!! What
is
this for:
strSQL = [INSERT SELECT STATEMENT HERE]

I don't understand that. I want to make this as 'dynamic' as possible.
In
terms of that goal, this line seems to be appropriate:
Set db = CurrentDb

Still something wrong. Here is the code that I'm working with now:
Private Sub Post_Click()
Dim err As Integer
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then

mydb = "C:\Pivot Trading System.accdb"
strSQL = mydb
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

'get the new record data
rstcontact.AddNew
'rstcontact!OptionsNo = OptionsNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

Set rs = Nothing
Set db = Nothing

End Sub

What am I missing? Also, as I stated initially, I don't know how to
handle
one thing in the code. In my Options table, 'Approved' is Data Type
Yes/No.
On the Form, I have a CheckBox named 'Approved'.
rstcontact!Approved = Approved

How do I convert this value on the Form to a DataType that the Table will
recognize? Maybe it requires an If.Then?

Thanks again for the help!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


David C. Holley said:
And on a side note, when you're adding data, if you're entering the data
via
a form that is bound to a record set (the form's recordset property is
set
to a table or SQL statement), Access will handle adding the data. Its
just a
matter of creating controls that are bound to their respective fields,
entering the data and then saving it by one of the many actions that will
cause the data to be comitted (shift+enter, save record on the
toolbar/ribbon, navigating to a previous record, moving to a new record,
etc.)




.
Click to expand...
Click to expand...
 
I kind of understand, but not quite. You say ‘if you're entering the data
via a form that is bound to a record set…Access can add the data’. I go to
Design View, click properties, click All, and see my fields in the Control
Source (down arrow). I think this means the data in the form is bound to the
table, right. I probably don’t even need VBA for this. I can probably do
the same thing with an Insert query, but I’ve never used an Insert query for
this, so I don’t know for sure how to set it up.

I’m still missing something here:
strSQL = [INSERT SELECT STATEMENT HERE]

What is the select statement?

I should probably use an Insert query, if that’s possible.

Any thoughts?

Thanks,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


David C. Holley said:
The variable strSQL is there to hold the select statement as in SELECT
fieldName1, fieldName2, fieldName3 FROM tableName.

But keep in mind my second post, if you're working with a form on which the
data is entered, the code becomes a moot point as Access can handle adding
the record.

ryguy7272 said:
Thanks for the help David! I'm definitely missing something here!! What
is
this for:
strSQL = [INSERT SELECT STATEMENT HERE]

I don't understand that. I want to make this as 'dynamic' as possible.
In
terms of that goal, this line seems to be appropriate:
Set db = CurrentDb

Still something wrong. Here is the code that I'm working with now:
Private Sub Post_Click()
Dim err As Integer
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then

mydb = "C:\Pivot Trading System.accdb"
strSQL = mydb
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

'get the new record data
rstcontact.AddNew
'rstcontact!OptionsNo = OptionsNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

Set rs = Nothing
Set db = Nothing

End Sub

What am I missing? Also, as I stated initially, I don't know how to
handle
one thing in the code. In my Options table, 'Approved' is Data Type
Yes/No.
On the Form, I have a CheckBox named 'Approved'.
rstcontact!Approved = Approved

How do I convert this value on the Form to a DataType that the Table will
recognize? Maybe it requires an If.Then?

Thanks again for the help!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


David C. Holley said:
And on a side note, when you're adding data, if you're entering the data
via
a form that is bound to a record set (the form's recordset property is
set
to a table or SQL statement), Access will handle adding the data. Its
just a
matter of creating controls that are bound to their respective fields,
entering the data and then saving it by one of the many actions that will
cause the data to be comitted (shift+enter, save record on the
toolbar/ribbon, navigating to a previous record, moving to a new record,
etc.)

Here's the code:
Private Sub Post_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\Pivot Trading System.accdb"
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mydb
cnn1.Open strCnn
' Open contact table.
Set rstcontact = New ADODB.Recordset
rstcontact.CursorType = adOpenKeyset
rstcontact.LockType = adLockOptimistic
rstcontact.Open "Options", cnn1, , , adCmdTable
'get the new record data
rstcontact.AddNew
rstcontact!OptionNo = OptionNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close
cnn1.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

When I run the code, I get a run-time error:
Multiple-step OLE DB operation generated errors. Check each OLE DB
status
value, if available. No work was done.

First Question:
In my Options table, 'Approved' is Data Type Yes/No. On the Form, I
have
a
CheckBox named 'Approved'. How do I convert this value on the Form to
a
DataType that the Table will recognize?

Second Question:
Also, the DB is working from my C-drive now, as evidenced here:
mydb = "C:\Pivot Trading System.accdb"

This DB will soon go on a network. Will I split the DB, as discussed
in
this link?
http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18


Then, have each front-end on the C-drive and still use that line of
code:
mydb = "C:\Pivot Trading System.accdb"

I guess I don't understand how Access will 'know' whether to go to the
network-drive or the C-drive. I've never done this before so I'm
looking
for
some advice as to how to proceed.

Thanks!
Ryan--



--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


.
Click to expand...
Click to expand...


.
Click to expand...
 
In Access 2007, if you go into DESIGN view and then click 'PROPERTIES' if
the property named 'RECORD SOURCE' has a value, then the form is bound.
Clicking the elipses to the right [...] will display the record source.

If the form IS bound, Access itself will take care of inserting, editing and
deleting the underlying records, provided that the controls on the form are
BOUND to the fields. In design view you'll see 'UNBOUND' or the name of the
field to which the control is BOUND. To bind a control, right click on the
control and select 'PROPERTIES'. Look for the one that reads 'CONTROL
SOURCE'. When you place the cursor there, you'll get a drop down list of the
available fields.

If you have the form and the controls BOUND, you do not need *ANY* queries.
Access will do the work entirely behind the scenes.

ryguy7272 said:
I kind of understand, but not quite. You say 'if you're entering the data
via a form that is bound to a record set.Access can add the data'. I go
to
Design View, click properties, click All, and see my fields in the Control
Source (down arrow). I think this means the data in the form is bound to
the
table, right. I probably don't even need VBA for this. I can probably do
the same thing with an Insert query, but I've never used an Insert query
for
this, so I don't know for sure how to set it up.

I'm still missing something here:
strSQL = [INSERT SELECT STATEMENT HERE]

What is the select statement?

I should probably use an Insert query, if that's possible.

Any thoughts?

Thanks,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


David C. Holley said:
The variable strSQL is there to hold the select statement as in SELECT
fieldName1, fieldName2, fieldName3 FROM tableName.

But keep in mind my second post, if you're working with a form on which
the
data is entered, the code becomes a moot point as Access can handle
adding
the record.

ryguy7272 said:
Thanks for the help David! I'm definitely missing something here!!
What
is
this for:
strSQL = [INSERT SELECT STATEMENT HERE]

I don't understand that. I want to make this as 'dynamic' as possible.
In
terms of that goal, this line seems to be appropriate:
Set db = CurrentDb

Still something wrong. Here is the code that I'm working with now:
Private Sub Post_Click()
Dim err As Integer
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then

mydb = "C:\Pivot Trading System.accdb"
strSQL = mydb
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

'get the new record data
rstcontact.AddNew
'rstcontact!OptionsNo = OptionsNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

Set rs = Nothing
Set db = Nothing

End Sub

What am I missing? Also, as I stated initially, I don't know how to
handle
one thing in the code. In my Options table, 'Approved' is Data Type
Yes/No.
On the Form, I have a CheckBox named 'Approved'.
rstcontact!Approved = Approved

How do I convert this value on the Form to a DataType that the Table
will
recognize? Maybe it requires an If.Then?

Thanks again for the help!
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


:

And on a side note, when you're adding data, if you're entering the
data
via
a form that is bound to a record set (the form's recordset property is
set
to a table or SQL statement), Access will handle adding the data. Its
just a
matter of creating controls that are bound to their respective fields,
entering the data and then saving it by one of the many actions that
will
cause the data to be comitted (shift+enter, save record on the
toolbar/ribbon, navigating to a previous record, moving to a new
record,
etc.)

Here's the code:
Private Sub Post_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\Pivot Trading System.accdb"
strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mydb
cnn1.Open strCnn
' Open contact table.
Set rstcontact = New ADODB.Recordset
rstcontact.CursorType = adOpenKeyset
rstcontact.LockType = adLockOptimistic
rstcontact.Open "Options", cnn1, , , adCmdTable
'get the new record data
rstcontact.AddNew
rstcontact!OptionNo = OptionNo
rstcontact!TicketNo = TicketNo
rstcontact!Side = Side
rstcontact!Symbol = Symbol
rstcontact!Quantity = Quantity
rstcontact!Strike = Strike
rstcontact!Call_Put = Call_Put
rstcontact!Price = Price
rstcontact!Exchange = Exchange
rstcontact!Approved = Approved
rstcontact!DateAdd = Now()
rstcontact.Update
' Show the newly added data.
MsgBox "New Post: " & rstcontact!OptionNo & " " &
rstcontact!TicketNo & " has been successfully added"
'close connections
rstcontact.Close
cnn1.Close

Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

When I run the code, I get a run-time error:
Multiple-step OLE DB operation generated errors. Check each OLE DB
status
value, if available. No work was done.

First Question:
In my Options table, 'Approved' is Data Type Yes/No. On the Form,
I
have
a
CheckBox named 'Approved'. How do I convert this value on the Form
to
a
DataType that the Table will recognize?

Second Question:
Also, the DB is working from my C-drive now, as evidenced here:
mydb = "C:\Pivot Trading System.accdb"

This DB will soon go on a network. Will I split the DB, as
discussed
in
this link?
http://www.access-experts.com/default.aspx?selection=TutorialSplitDB&sm=18


Then, have each front-end on the C-drive and still use that line of
code:
mydb = "C:\Pivot Trading System.accdb"

I guess I don't understand how Access will 'know' whether to go to
the
network-drive or the C-drive. I've never done this before so I'm
looking
for
some advice as to how to proceed.

Thanks!
Ryan--



--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top