Placing text data into an existing database file 3

[vaughn9]

I am trying to send text box data from a vb form to an existing access database. For example if I have 4 text boxes with data about one customer eg. name address age
I want to be able to input the data in the vb text boxes then click on a command button which would send the data as my first record to my database. The database would already have been created with all the field names and fields.

 

[robctech]

Not sure if this is what you want but place this code in the submit button on click event

Dim myCon as New ADODB.Connection

myCon.Execute("Insert TableName (Field1, Field2, Field3) values ('" & textbox1 & "', '"& textbox2 & "', '" & textbox3 & "'")

 

[zemp]

An easy method with bound controls.

1. Add a data control or ADO data control to your form and bind it to the database table. You can use the wizards supplied for this. Set the .eofAction (or similar property) to allow addition or records (for ADODC: 2 - adDoAddNew)

2. Bind the text boxes to the data control (via the .datasource and .datafield properties).

You can use the buttons on the data control to navigate the table or create your own set of buttons.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[vaughn9]

what does mycon represent

here is my code
Dim mycon As New ADODB.Connection

mycon.Execute ("stationtable (Field1, Field2, Field3) values ('" & Text1 & "', '" & Text2 & "', '" & Text3 & "'&quot

My database exists and is called station. The table in my database is called stationtable. I placed the above code in the send button but I am getting an error message that says code cannot run while object is open. Is ther something I am missing. I chose ado 2.5 ref

How does the code know which database to find the table name in.

 

[zemp]

'mycon' is the ADO connection object that is connected to the database. You will first need to create a connection string and open the connection. If the database is located in the application folder you can use the following

Set mycon = New ADODB.Connection
   mycon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\station.mdb;Persist Security Info=False"
   mycon.Open

Otherwise your 'Data Source' will have to be the valid path to the database. Then you can execute and SQL query on your database. Although using ADO parameters is safer (see faq709-1526),

mycon.Execute "INSERT INTO station (Field1, field2, field3) VALUES ('" & text1.text & "','" & Text2.text & "','" & Text3.text & "')"

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[AndyWatt]

> what does mycon represent
> Dim mycon As New ADODB.Connection <-- this perhaps?

mycon.Execute ("[b]INSERT INTO[/b] stationtable (Field1, Field2, Field3) values ('" & Text1 & "', '" & Text2 & "', '" & Text3 & "'")

Substitute your fieldnames from stationtable for the ones in the code sample.

Andy
&quot;Logic is invincible because in order to combat logic it is necessary to use logic.&quot; -- Pierre Boutroux
&quot;Why does my program keep showing error messages every time something goes wrong?&quot;

 

[johnwm]

You actually need to set the new connection to point to a database:
[tt]
Dim mycon As New ADODB.Connection
strPathtoDB = App.Path & &quot;\station.mdb&quot;
strCn = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & &quot;Data Source = &quot; & strPathtoDB3
mycon.Open strCn
mycon.Execute (&quot;Insert stationtable (Field1, Field2, Field3) values ('&quot; & Text1 & &quot;', '&quot; & Text2 & &quot;', '&quot; & Text3 & &quot;'&quot
[/tt]
Don't forget to close your connection when you're finished with it

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[vaughn9]

Here is my code

Dim mycon As New ADODB.Connection


Set mycon = New ADODB.Connection
mycon.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & App.Path & &quot;\station.mdb;Persist Security Info=False&quot;
mycon.Open



mycon.Execute &quot;INSERT INTO stationtable (Name, Address1, Aaddress2) VALUES ('&quot; & Text1.Text & &quot;','&quot; & Text2.Text & &quot;','&quot; & Text3.Text & &quot;')&quot;

the error I am getting reads syntax error in insert into statement

 

[zemp]

Is 'Aaddress2' spelled correctly? doesn't look like it. Make sure that all table and field names are correct.

Take Care,

zemp

&quot;Show me someone with both feet on the ground and I will show you someone who can't put their pants on.&quot;

 

[vaughn9]

It is working perfectly now but how do I close the connection

 

[zemp]

To close the connection and destroy the object, add the following code at the end of your procedure.

mycon.close
set mycon=nothing

Take Care,

zemp

&quot;Show me someone with both feet on the ground and I will show you someone who can't put their pants on.&quot;

 

[vaughn9]

How do I show the database without actually going to it and opening it.

I think I need code that will toggle between show and hide so that I can view and hide the database without actually goint to it and opening it.

 

[zemp]

I am not sure what you mean. Are you talking about opening a connection or opening MS Access?

You can display the information from a table or query in VB with a data grid or a MS flexgrid. But you will need a connection. The easiest way, if you just want to display the data, would be to bind the grids to a table or recordset (query).

Please explain further if I missed the mark.

Take Care,

zemp

&quot;Show me someone with both feet on the ground and I will show you someone who can't put their pants on.&quot;

 

[vaughn9]

if I were dealing with an excel object instead of a database object I would say

' toggle Excel visibility
moXL.Visible = (Not moXL.Visible)

or

moXL.Visible = True


I need a button that would allow me to click on it and make the database visible. right now i can send the text data to db but if I want to view the records I sent I would have to go to access itself and open the database. i need to open the database to view that data from vb. So the code button should prob have a label named show/hide so that when I click on it the database is visible or when I click on it again the database is hidden

 

[Dominator22m]

Hi there,

Add a datagrid to the form, bind a recordset to the grid then in the click event of your button :-

datagrid1.visible = not datagrid1.visible

Hope this helps

Brian


Bad spellers of the world untie!!!

 

[vaughn9]

Ok..I see what you mean but the only thing is that my data from my text boxes are not showing in the data grid. my code reads

================================================
Dim mycon As New ADODB.Connection

Set mycon = New ADODB.Connection

mycon.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & &quot;c:\my documents\info.mdb;Persist Security Info=False&quot;
mycon.Open
===================================================

my code successfully sends the data in the text boxes to the access database table, but if I were using the data grid how do I get my text box data in the data grid


mycon.Execute (&quot;Insert into infotable (surname, firstname, age) values ('&quot; & Text1 & &quot;', '&quot; & Text2 & &quot;', '&quot; & Text3 & &quot;')&quot


mycon.Close
Set mycon = Nothing

 

[vaughn9]

Ok..I see what you mean but the only thing is that my data from my text boxes are not showing in the data grid. my code reads

================================================
Dim mycon As New ADODB.Connection

Set mycon = New ADODB.Connection

mycon.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & &quot;c:\my documents\info.mdb;Persist Security Info=False&quot;
mycon.Open

mycon.Execute (&quot;Insert into infotable (surname, firstname, age) values ('&quot; & Text1 & &quot;', '&quot; & Text2 & &quot;', '&quot; & Text3 & &quot;')&quot

my code successfully sends the data in the text boxes to the access database table, but if I were using the data grid how do I get my text box data in the data grid




mycon.Close
Set mycon = Nothing

 

[zemp]

The easierst way would be to use a bound ADODC and a bound Datagrid. A serarch with google will give plenty of results. Here are a couple of links to get you started.

http://www.vb-helper.com/howto_get_datagrid_values.html

http://courses.cs.emporia.edu/pheattch/courses/2002/cs444f02/hand24/hand24.htm

Take Care,

zemp

&quot;Show me someone with both feet on the ground and I will show you someone who can't put their pants on.&quot;