Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:


I know am asking a lot! But here's the deal!.. I have a form with like 10 text boxes. How can I save whatever is inside the text book into a table in ACCESS using a click of a button or any other way!
Please I need the answer before 16th of March!
  • 7
  • 2
  • 2
1 Solution
Do you have to create the Access table pogramatically or can we assume it already exists?

If it already exists.. its kinda easy to do.. Overview steps:

1.. Add a Data Control to your form. In the DataBaseName Property place the File name of your Access database.. eg. C:\pathname\NWind.MDB (the property page has a browse feature).. and then in the Recordsource property place the table you want to use (if the Databasename is already specified, a dropdown list of tables will appear.

2.  Add 10 Text boxes. In each Textbox, change its DataSource property to read Data1 (there is a dropdown once the Data control has been defined).. and change the DataField property to the Table Field you want to use in this Textbox. (Tip.. also change the Textbox.Text property to the field name it will make the next step easier.

3.  Add 10 Labels and align them next to the Textboxes so that the user will know what each Textbox is for.

Your very basic data update program is now complete.. <smile>. Just type into a textbox, and your change is immediately recorded in the Database.
Ok. Here:

In your project, add the reference to the MS ActiveX Data Objects Lib.

Now, do this:

Dim objCommand As ADODB.Command
On Error GoTo Error_Handler  
  ' make sure vntError isn't holding anything
  vntError = ""
  Set objConnection = New ADODB.Connection
  Set objCommand = New ADODB.Command

The following code makes a connection to the database through a DSN. However, this is not required, and I suggest using a DSNless connection whenever possible (using connecting string: "driver={Microsoft Access Driver (*.mdb)};dbq=C:\path\to\database\database.mdb"). When an application is using a DSN it is also using ODBC. Although this works, the application isn’t taking advantage of native OLE DB providers for the data source. When a native provider is used, performance is usually better and functionality is greater. A DSN connection uses OLE DB which uses a generic ODBC provider, which uses ODBC to forward on the calls to the data source.

  ' open a connection to the datasource
  With objConnection
    .ConnectionString = "DSN=MyDSN"
    .CursorLocation = adUseClient
    .Mode = adModeReadWrite
  End With
Once a connection is open, the new record is added to the database through a simple INSERT statement. Any errors that arise in the routine are handled in the Error_Handler: section. This section takes the description, number, and source of the error and puts it into the vntError variable. This allows the client to check the variable to see if the routine succeeded or failed.

  ' add the contents of the txt ctrl.
  With objCommand
    Set .ActiveConnection = objConnection
    .CommandText = "INSERT INTO MyTable(Fieldname) VALUES('" &            
                    txtCtrl.text & ");"
    .CommandType = adCmdText
  End With
  'clean up
  Set objCommand = Nothing
  If objConnection.State = adStateOpen Then objConnection.Close
  Set objConnection = Nothing
  Exit Sub
  vntError = Err.Description & " (" & Err.Number & " - " & Err.Source & ")"
  Resume Exit_Handler

That will add the text in the txtCtrl.text into the Fieldname field in the Authors table of the database that the MyDSN points to.

Coolass.. how can you lock this question with an answer like that?

Locking the question presumes that this is the best and most definitive answer to the question.. and that no other expert need comment on any other solution. In my opinion, you are bold in concluding this with your answer.. and for the following reasons.

1.  The scope of this question is not entirely clear. Both you and I are ASSUMING that the Access table he wants to port to, already exists.. but what if it doesn't?.. Code would have to be added to create it. The questioner should have been more specific.

2.  The questioner indicates that there are 10 Textboxs, your solution only provides for one. Additionally, do the 10 Textboxs represent one record, or are they to be treated as 10 separate records? Once again the scope of the question is unclear. Your example would create a table record for each textbox.. mine treats the 10 boxes as a recordset.. both of us are assuming.. (but one of us is right.. LOL).

3. Correct me if I am wrong, but isn't the ADO defautlt LockType Read Only?

Anyhow.. I don't want to get too anal here. My only point is this.. give others a chance to comment before you lock an answer. Believe me your comments are well appreciated.. and well deserved of being posted here.. but let's give others a chance to comment.. and the questioner the opportunity to fully define his/her requirement before locking da sucka down.. <smile>.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I felt my answer was good enough, considering he is only offering 40 points. And yes, the ADO default lock is read only, that's why I said:

..Mode = adModeReadWrite

By posting this code he could use it to easily write his own code to do all 10 text boxes...

He didn't ask how to create the Access database, if he had I would have told him how to. His question was:

"How can I save whatever is inside the text book into a table in ACCESS using a click of a button or any other way!"

And that's what I answered. To say that I should wait for people to post a bunch of comments before I give an answer which I beleive is correct is silly.
Bunch of comments.. Is Silly?.. Isn't the questioner the judge of that?..

By locking the question down.. you severely limit the questioner's ability to get answers/comments from other experts. If this question were still open, chances are good there would be more comments here than our paltry two.

And why would other comments be such a bother to you? If your answer is the best one (which it could be) then the questioner will accord you his points accordingly.. or other experts would pass over responding to the question in the knowledge that your answer is correct and definitive. I mean, what's wrong with a little competition.. and may the best answer win?

Ultimately, the questioner is the judge of all this. It is now his/her choice.. to accept your answer (or reject it).. accept my answer (or reject it).. or to reopen the question for further comment. The choice is entirely his/hers.. <smile>.

Good chatting with ya..


Oh.. and by the way Coolass.. I missed seeing the Mode statement.. <d*mn bifocals and 14 inch screen.. LOL>.. Thank YOU for pointing it out.. <smile>.

hello_200Author Commented:
Wsh2 I already know how to get information from an access database plus am not getting information from there I am putting data into it. The data which I am giving Access is from

coolAss I have tried your way but it keeps giving me an error when I try to run the program "Invalid Outside Procedure". Shall I insert the code you gave me on Form load or in general or what exactly?????.....

By the way I already have a database and the fields already exist! Like i said whatever is inside the text book into a table in ACCESS using a click of a button or any other way!
As CoolAss has covered the ADO methodology (which IS the current Microsoft standard.. <smile>), I will resort to covering the old, more plebian DAO Method.

Add a reference in your project to "Microsoft DAO 3.51 Object Library"

<----- Code Begin ----->

Public Sub AddRecord2Access()

    Dim wsNwind As Workspace
    Set wsNwind = CreateWorkspace("", "admin", "", dbUseJet)
    Dim dbNwind As Database
    Set dbNwind = wsNwind.OpenDatabase("c:\path\YourDataBaseName.mdb")
    Dim rsNwind As Recordset
    Set rsNwind = dbNwind.OpenRecordset("YourTableName")
    With rsNwind
        !FieldName1 = Text1.Text
        !FieldName2 = Text2.Text
        !Fieldname3 = Text3.Text
        !Fieldname4 = Text4.Text
        !Fieldname5 = Text5.Text
        !Fieldname6 = Text6.Text
        !Fieldname7 = Text7.Text
        !Fieldname8 = Text8.Text
        !Fieldname9 = Text9.Text
        !Fieldname10 = Text10.Text
    End With

<----- Code End ----->

Perhaps you are right Coolass.. for 40 points its just not worth it.. <sigh>.
Oh well.. <smile>.
hello_200Author Commented:
wsh2 Nothing is in my database whenever i enter something in the field and close the VB application there's nothing added to the database?!?!

Add a debug breakpoint (or a Msgbox) to the code that I provided to make sure that you are cycling through the .addnew / .update code.
Atta Coolass.. <smile>
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now