Solved

HELP

Posted on 2000-03-10
11
143 Views
Last Modified: 2010-05-02
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!
Thanks
0
Comment
Question by:hello_200
  • 7
  • 2
  • 2
11 Comments
 
LVL 14

Expert Comment

by:wsh2
ID: 2607310
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.
0
 
LVL 4

Accepted Solution

by:
CoolAss earned 40 total points
ID: 2607317
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
    .Open
  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
    .Execute
  End With
     
Exit_Handler:
  'clean up
  Set objCommand = Nothing
  If objConnection.State = adStateOpen Then objConnection.Close
  Set objConnection = Nothing
  Exit Sub
     
Error_Handler:
  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.

0
 
LVL 14

Expert Comment

by:wsh2
ID: 2607383
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>.
0
 
LVL 4

Expert Comment

by:CoolAss
ID: 2607388
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.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2607464
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..

Bill.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:wsh2
ID: 2607467
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>.

Bill.
0
 

Author Comment

by:hello_200
ID: 2608936
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
Excel.

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!
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2609033
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
        .AddNew
        !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
        .Update
    End With
   
    rsNwind.Close
    dbNwind.Close
    wsNwind.Close

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

Perhaps you are right Coolass.. for 40 points its just not worth it.. <sigh>.
Oh well.. <smile>.
0
 

Author Comment

by:hello_200
ID: 2609137
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?!?!

0
 
LVL 14

Expert Comment

by:wsh2
ID: 2609168
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.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2615059
Atta Coolass.. <smile>
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now