I am using MSAccess97 & VB6.
Created a DB using Visual Data Manager.
How to insert into DB usng the recordset from the forms.
Please reply

Hello.
Are you using ADO or DAO to connect to the db? Anyway there's no obscure trick to do it: either link your controls to the recordset fields, or execute an Update method when you add/edit your records.

If you'd be more specific, we could say something more.
Hope it helps.

____________________________
Real Programmer can count up to 1024 on his fingers

Hi Guru,
   Thanks for the response..I am using VB6 with MSAccess97
Using DAO
Pls clarify my following doubts:
1)     How to  populate a listbox/combobox other than using
AddItem in Form_Load as there are more datas.
2)I am storing the login name & password in DB.How to encrypt the login name & password?
3)when i select an item in a combobox,based on the selection in the first combo,the data should be populated in the second combobox?how to go about.
Sorry,the list goes as I am naive to our VB
Thanks to VBGuru

quote:

---

yronium wrote:
Hello.
Are you using ADO or DAO to connect to the db? Anyway there's no obscure trick to do it: either link your controls to the recordset fields, or execute an Update method when you add/edit your records.

If you'd be more specific, we could say something more.
Hope it helps.

---

Since I am new to VB,I am striving to get with & totally depending on the net

I am using DAO,

            Private Sub Command1_Click()
Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase("C:\indiacars.mdb", False, False, ";pwd=")
Set rs = db.OpenRecordset("GeneralDetails")
rs.AddNew

rs("Registration Number") = Text1.Text
rs("Vehicle Make") = Combo1.Text
rs("Kind Of Owner") = Text2.Text
rs("Date Of Buying") = Text3.Text
rs("Type Of Tax") = Option1.Value
rs("Annual Tax") = Val(Text4.Text)
rs("Comments") = Text5.Text
rs("Insurance") = Text6.Text
rs("Customer Name") = Text7.Text
rs("City") = List2.Text
rs("Others") = Text9.Text
rs("State") = List3.Text
rs("ZipCode") = Val(Text11.Text)
rs("Phone Number") = Val(Text12.Text)
rs("Fax Number") = Text13.Text

rs.Update
rs.Close
db.Close

End Sub

I am writing the above to insert into DB(MSAccess97)
My Queries:
              1)    The registration number(field in control1) present in Form1 should be updated in the form2.
            2) When I select item in list box( registration number) in form2 a few data should be updated in the controls(textboxes) in form2.
            3)when an item is selected in a combobox,based on the selected item data should be populated in other listbox.how can it be done.
Pls need the quick response.anybody

Thank you    

quote:

---

1)    The registration number(field in control1) present in Form1 should be updated in the form2.

---

quote:

---

2) When I select item in list box( registration number) in form2 a few data should be updated in the controls(textboxes) in form2.

---

quote:

---

3)when an item is selected in a combobox,based on the selected item data should be populated in other listbox.how can it be done.

---

Please Help! I've read your reply on inserting data from VB6 to Access 97...but how is it done using Access 2000 and ADO.   I have searched the forum for this info time and time over but im a serious VB newbie, infact u cud call me a complete programming newbie!!

Please please just try and explain what you said as simply as possible for a simple mind..cause although all yronium said about moving to the last record makes sense..i jus dont know how to do it!  
Please help! for the last couple of days this is exactly how ive felt..

My dear "complete-programming-newbie!!", hello and welcome in the forum.
Are you sure you searched in the forum about your doubts? Here is only the latter post about connecting ADO-VB6, and notice there are some links in it that you can follow.
Connecting VB6 to a database by ADO is not a three-rows issue, and we could be more helpful if you'd post some code of what you've done at this time.

Anyway, in general words:
when you need to link a database to your project - say, filling your textboxes with database values - you can use ADO. (Or DAO, or RDO instead, or JRO, but these are other technologies. ADO is the most recent, and the most used.) So first, you should add ADO to your Reference window in the Project menu. "ADO" means "Microsoft ActiveX Data Objects 2.x Library"; once you checked it into that window, when you declare a variable into your code by writing "Dim x As ..." in the dropdown list of IntelliSense is avaliable the ADODB option, and with a dot you will be prompted with another type list. So you can declare "Dim cn As ADODB.Connection, rs As ADODB.Recordset..." etc.

Once you declared an ADODB.Recordset variable (a Recordset variable can contain a whole database table), you can link it to your external db file. In order to do this, you have to use a Connection object. The Connection object manages the "dialogue" between ADO and the db file. The Connection is tuned by setting all its parameters (such as the file name, the dataprovider name, etc.) by a ConnectionString. So, you build your ConnectionString and then open the connection (remember to close it and set it on Nothing when you're finished. And so the recordset object as well).

When the connection is open, you can use it to fill your recordset variable with the database value. The Recordset.Open method requires as an argument an open connection to use. And the Recordset object exposes an ActiveConnection property that says which connection the current recordset is open with. In fact you could have two or three connections open at one time, and be connected with two or three different db files, and use them all together to fill many recordsets with many tables or queries from the files.

Now you have a whole table stored int a recordset variable, and you have a form, with its textboxes waiting for data. You can set for each textbox its DataSource property on your recordset variable, and its DataField property on the name of a specific field from the recordset. So the textbox is bound to the value of this field, and when you move from one record to another, the value in the textbox changes. To navigate the recordset you can use one of the Recordset.Movexxxx methods (so, for instance, to move to the last record yo can write rs.MoveLast), and you can build your own recordset browser by a simple array of command buttons, and setting their Click events with the appropriate method.

This is the theory. I have only one thing more to say: you can easily do all these things if you add an Adodc control into your form. The Adodc control is a recordset browser that manages the whole recordset, so you can set all the textboxes' DataSource property on it. To have it avaliable in your control toolbar, you have to add a Microsoft ADO Data Control checkmark into the Project/Components window.

Well I hope is enough to start, and hope I have been clear. You didn't specify your level of complete-newbie-ness, so maybe you knew already many things I wrote down. You can find plenty of sample code here and along the web.
Hope it helps. Ask again and post your code if there's anything not so clear.

____________________________
Real Programmer can count up to 1024 on his fingers

Hi..
         guru..Happy to c such a descriptive mail..yes I was able to proceed..Thanks for your brief reply..
I would like to know how can we compare 2 dates in VB??
Pls let me know..
Also what abt the migration from VB to VB.Net??
how viable is it??

Thank You
  

Hai Sumathi
R u from tamilnadu.WEll reg ur question to compare two dates.U can  use the datediff() function.Check out for datediff() function in MSDN.If u need more guidance then please post here.
And i hope so if u have sound knowledge in VB and OOPS then its easy to migrate to .Net as i am doing it now.

I have to say i wasnt expecting such a descriptive and helpful mail so thanks a mill! but i havent completely solved my problem...when i say im new to programming i mean i picked up 'Sams teach yourself VB6 in 24hrs' a couple of weeks ago..and thats as far as my VB tution has gone..i have been doing research on the web though so it helps...and ofcourse lovely helpful beings like u.. ...now i actually understand what im s'posed to b trying to achieve..x
Anyway here is the code ive written so far:

Private Sub cmdConnect_Click()
'To link application to database
'First setup a connection object

  Dim cn As ADODB.Connection, rs As ADODB.Recordset
  Dim db As Database
  Set cn = New ADODB.Connection
'Next I must build a connection string and set it to an Open method
'for my specifications Database
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=p:\specifications.mdb"
  Set db = OpenDatabase("p:\specifications.mdb")
  Set rs = db.OpenRecordset("tblClients")
End Sub

but i get an error message saying " Run time error '3343' : unrecognized database format p:\specifications.mdb"..
what to do

Maybe it was the effect of a good nights sleep but i figured out what the problem was and it works!
My only problem now is now anytime i execute the command it adds as many records as i click. is there a way to prevent this?? Here is my code below:

Private Sub cmdConnect_Click() 'To link application to database 'First setup a connection object   Dim cn As ADODB.Connection, rs As ADODB.Recordset   Dim db As Database   Set cn = New ADODB.Connection 'Next I must build a connection string and set it to an Open method 'for my specifications Database   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _                    "Data Source=p:\specifications.mdb"   Set rs = New ADODB.Recordset   'The next step is to open the recordset      rs.Open "tblClients", cn, adOpenKeyset, adLockPessimistic, adCmdTable   'Cursor type is Keyset and is preferred in a multi-user database 'where changes by other users are not needed immediately 'The Lock Type is Pessimistic which ensures other users are locked 'out for the entire period that the records are being accessed and modified      rs.AddNew     'This statement defines the BOF   rs("Job#") = txtJobNo.Text   rs("Date") = txtDate.Text   rs("Sales Person") = cboSalesPerson.Text   rs("Approx del date") = txtDeldate.Text   rs("Issue#") = txtIssueNo.Text   rs("Client") = txtClient.Text   rs("Client Address 1") = txtClientAddressLine1.Text   rs("Client Address 2") = txtClientAddressLine2.Text   rs("Client Address 3") = txtClientAddressLine3.Text   rs("Client Post Code") = txtClientPostCode.Text   rs("Telephone No") = txtClientTelNo.Text   rs("Contact") = txtClientContact.Text   rs("Site name") = txtSite.Text   rs("Site Address 1") = txtSiteAddressLine1.Text   rs("Site Address 2") = txtSiteAddressLine2.Text   rs("Site Address 3") = txtSiteAddressLine3.Text   rs("Site Telephone No") = txtSiteTelNo.Text   rs("Site Fax No") = txtSiteFaxNo.Text   rs("Site Contact") = txtSiteContact.Text   rs("Company representative") = txtSiteVisit.Text   rs("Other company contacts") = txtOtherCon.Text   rs("Existing Drawings") = optDrawYes.Value   rs("Existing Photos") = optPhotoYes.Value   rs("Foundation drawings") = txtFoundation.Text   rs("Layout drawings") = txtLayout.Text   rs("Cable drawings") = txtCable.Text      rs.Update   rs.Close   'Closes record   cn.Close  'Closes the connection End Sub

quote:

---

lolly wrote:
Maybe it was the effect of a good nights sleep but i figured out what the problem was and it works!

---

Hi
      Using the package wizard, I try to deploy my files in another system connected in LAN.
when I click the setup,it gives err saying that "some of the system files in your system are out of date"
y is it so
Pls reply guru..
Thanks  

Sumathi
What s ur client system configuration.If its ask for updating just give as update and then continue with installing.

quote:

---

sumathige wrote:
Hi
      Using the package wizard, I try to deploy my files in another system connected in LAN.
when I click the setup,it gives err saying that "some of the system files in your system are out of date"
y is it so
Pls reply guru..
Thanks  

---

Sorry i dont have internet access at home so ive been away for quite a while..lol...yup i guess it definitely didnt include sleep..

quote:

---

yronium wrote:
Usually the connection and the recordset are open in the Form_Load event, and they are set on Nothing and closed in the Form_Unload event. Then, every single operation is assigned to some buttons.

---

I just thought i should point out that I particularly want to know how to inset and if statement into a recordset routine. Just so it is more specific...x

quote:

---

lolly wrote:
I tried to cut the first bit of the text and insert it in the form load event but i got an error message saying that arguments are of the wrong type, are out of acceptable range or in conflict with one another....

---

yes i wud b interested in a sample project and i totally see ur point about private mail. In the mean time im still searching the forum for info on how to print an Access report from VB6....i cant seem to make the data report work....

like i said ive got 4 forms in VB6 and i have decided to create the same forms in access. this is because i want to be able to print out the data typed in by the user in the textboxes. It currently works in the sense that i have successfully linked the textboxes to the database..but now how do i insert a report of some sort that will print out the data exactly as i want it?   please help...x

I attach here a small sample project, using an .mdb file with two tables. In the form there is a DataCombo control instead of a normal combobox. The whole project is largely commented, so have a look on it. Just, I made the form to manage only one of the two tables, so it needs a little more workaround to be complete. When I've finished I'm gonna ask admin to post it in the download section.

To print an Access report from VB, have a look at the code in this thread of some weeks ago.

Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers

____________________________
Attached:
VBtoMDB.zip 100 KB (Downloads: 17)

You're a star! I kinda understand most of it..and ive been trying to manipulate it for my project..however in the form load event ive come across an error msg that says 'either BOF or EOF is true or the current record has been deleted. Requested operation requires a current record.'
the code is below:

Private Sub Form_Load() ' set the pointer during the load phase     Screen.MousePointer = vbHourglass     ' create and open the connection     Set cn = New ADODB.Connection     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _         & "Data Source=" & App.Path & "\specifications.mdb;" _         & "Mode=ReadWrite|Share Deny None;Persist Security Info=False"     cn.Open Set rs = New ADODB.Recordset   'The next step is to open the recordset   rs.CursorLocation = adUseClient   rs.Open "tblClients", cn, adOpenKeyset, adLockPessimistic, adCmdTable   'Cursor type is Keyset and is preferred in a multi-user database 'where changes by other users are not needed immediately 'The Lock Type is Pessimistic which ensures other users are locked 'out for the entire period that the records are being accessed and modified rs.Sort = "Job#" rs.MoveLast     ' move to the last record rsLastID = rs.Fields("Job#").Value      ' ...and read its ID ' store in a variable the number of records rsCount = rs.RecordCount rs("Job#") = txtJobNo.Text   rs("Date") = txtDate.Text   rs("Sales Person") = dcbSalesPerson.Text   rs("Approx del date") = txtDeldate.Text   rs("Issue#") = txtIssueNo.Text   rs("Client") = txtClient.Text   rs("Client Address 1") = txtClientAddressLine1.Text   rs("Client Address 2") = txtClientAddressLine2.Text   rs("Client Address 3") = txtClientAddressLine3.Text   rs("Client Post Code") = txtClientPostCode.Text   rs("Telephone No") = txtClientTelNo.Text   rs("Contact") = txtClientContact.Text   rs("Site name") = txtSite.Text   rs("Site Address 1") = txtSiteAddressLine1.Text   rs("Site Address 2") = txtSiteAddressLine2.Text   rs("Site Address 3") = txtSiteAddressLine3.Text   rs("Site Telephone No") = txtSiteTelNo.Text   rs("Site Fax No") = txtSiteFaxNo.Text   rs("Site Contact") = txtSiteContact.Text   rs("Company representative") = txtSiteVisit.Text   rs("Other company contacts") = txtOtherCon.Text   rs("Existing Drawings") = optDrawYes.Value   rs("Existing Photos") = optPhotoYes.Value   rs("Foundation drawings") = txtFoundation.Text   rs("Layout drawings") = txtLayout.Text   rs("Cable drawings") = txtCable.Text   rs.MoveFirst 'this instruction has to be put to cause another event    ' of the MoveComplete on the recordset in order to update the NumberOfRecord    ' textbox Screen.MousePointer = vbDefault End Sub

You didn't specify what row gives the BOF And EOF error, but I can guess it sould be one of the MoveFirst, MoveLast or Sort methods, or reading the RecordCount property. Anyway it means that, even if there are no records in the recordset, your code makes an attempt to read the records. I could be more specific if I knew the line that raises the error.

I hope you know what you'are doing, but there is no need to manually set the textboxes values: in fact you can set their DataField property on the correspondant field name and the DataSource property on your recordset (rs). The rest is automatically made by ADO. Have a try.

Feel free to post again.

____________________________
Real Programmer can count up to 1024 on his fingers

Hi,


The steps are as follows.

1.Validate the data before inserting into tables.
2.Append a blank record in the table and assigns data entered in text boxes to fields of table and update the record to the table.
3.Clear the textbox controls.

If you want full code to develop a package , try this book

"Develop an accounting package using vb", try  the client/server version. You will get to know how to create a single form and use it for many transactions, how to create a class for database application etc..

  

Its the rs.MoveLast row.

No i dont know what im doing...lol..but i will try to just set the datafield property and datasource...but how will it know wot fields to insert my data into..if that sounds like a stupid question..pls forgive me but u hav to understand that i have no real IT skills apart from word processing and other such day to day skills....x

Hai
CAn u please explian as what is ur exact expectation.
If u explain it in detail it will be easy for me to solve ur problem

quote:

---

rs.Sort = "Job#" rs.MoveLast     ' move to the last record

---

yeah i figured out that may be the problem so i added a blank record...just to start off...is this alright? I have also attached my VB application...pls help me check...dont know y but when i click the new, it disables all controls. this is not wot ive inputted in my code but just wondering if i have missed something!

oops..

____________________________
Attached:
frmClientDetails.zip 3 KB (Downloads: 4)

No need for adding record, you just check if it has an records returned first

if not (rs.bof and rs.eof) then
    rs. move last
    ' and allthe rest of you code goes here
end if

____________________________
If you find the answer helpful, please mark this topic as solved.

Yeah, of course every MoveXXXX method raises an error if the recordset is empty, so we got to manage this case.

Finally, my sample project is ready and everybody can download it here. In the zip file I provided two databases: an mdb with some records in, and another mdb that's empty. So you can test it in both cases. I'm exepecting readers report me any bug they find, but I made it as a tutorial on how managing an mdb database by VB and ADO.
It is a simple tutorial, so I removed all the Debug.Print instructions I used to test each step. If I wouldn't, it'd become more complex, but in fact I want to remind you that managing a db is not a basic topic, and if you want to get deep into it, you'd figure out each case and deal with all the various parameters, errors, events, status variables and other stuff ADO includes.

Hope it will be helpful and you like it.

____________________________
Real Programmer can count up to 1024 on his fingers