Visual Basic and Access database

[zucchini]

Would appreciate a high level non technical Visual Basic overview with supporting Access database in an NT environment, and the differences between an NT and UNIX operating systems.<br>Thank you very much

 

[BruceHesher]

There are 2 general ways that VB has to interact with an Access database.&nbsp;&nbsp;One is to use the data control and the other databound controls.&nbsp;&nbsp;This way is fast and fairly easy but is limited.&nbsp;&nbsp;The other is to dimension databse objects then use the DBEngine. ex:<br><br>&nbsp;'dimension DAO objects at module level<br>&nbsp;&nbsp;&nbsp;&nbsp;Global ws As Workspace<br>&nbsp;&nbsp;&nbsp;&nbsp;Global db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Global rs As Recordset<br><br>&nbsp;'then in event handlers you can open databases,&nbsp;&nbsp;&nbsp;&nbsp;recordsets, etc.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;'create a workspace<br>&nbsp;&nbsp;&nbsp;&nbsp;Set ws = Workspaces(0)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;'open a database<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = ws.OpenDatabase(app.path & &quot;\Some.mdb&quot;, False, False, dbLangGeneral & &quot;;pwd=nice_try&quot<br><br>'you can now insert into tables or use any SQL.<br>&nbsp;&nbsp;&nbsp;&nbsp;db.Execute &quot; INSERT INTO MyTable IN 'a:Some.mdb' SELECT * FROM SomeTable WHERE Subject = '&quot; & dbcSubject.Text & &quot;'&quot;<br><br>

 

[philcha]

Using the DBEngine object and its methods, collections, etc. allows a VB program to do fancy things like create DBs, create, modify and delete tables within DBs, report on the DB structure, etc.<br><br>Using the data control is usually much easier if you don't want to play with the DB structure.&nbsp;&nbsp;I prefer not to use bound controls as changes to them are applied immediately to the DB.&nbsp;&nbsp;But you can use the data control without using bound controls, e.g.<br>* use the data control to build a recordset and move through it.<br>* assign the columns of the current record to unbound controls, e.g.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TextBox1.Text = DataControl1.Recordset(&quot;Field1&quot<br>* validate any changes made by the user.<br>* set the columns of the current record from the unbound controls, e.g.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DataControl1.Recordset(&quot;Field1&quot = TextBox1.Text <br><br>Sometimes you want to use a particular type of Active X control and this may force you to use one approach or the other.

 

[chiph]

zucchini -<br><br>BruceHesher and philca have addressed the data control subject very well.&nbsp;&nbsp;Access can be used in a multi-user database, but tends to run out of steam at about 4 users (YMMV).&nbsp;&nbsp;We use it for storing user & application specific data.&nbsp;&nbsp;Like if there's some configuration info that is too big, or too awkward to put into the Windows Registry or a .INI file, we'll use an Access database.&nbsp;&nbsp;We also make sure there's a way for the support desk to have the user rebuild the Access database should it become corrupt (we ship a small program that that's all it does, or sometimes add a command-line switch to rebuild the database, etc).<br><br>Anything beyond this, we spec a larger database, like Oracle or MS SQL Server.&nbsp;&nbsp;Sometimes it's overkill, but they're much sturdier than Access.&nbsp;&nbsp;Mostly, it comes down to preferences of me and the other developers.<br><br>Regarding the differences between Unix and NT.&nbsp;&nbsp;The big one is that NT is single-user.&nbsp;&nbsp;You can buy multi-user add-ons like MS Terminal Server or Citrix WinFrame, but they're still add-ons.&nbsp;&nbsp;Reliability of both is pretty good, but a well-run Unix box is still much better than a well-run NT box (we have to reboot our NT Servers about every 2 weeks for preventative reasons).&nbsp;&nbsp;The development tools available for NT give the developer a much more productive work environment.&nbsp;&nbsp;At the same time, they tend to hide some of the low-level details that a programmer needs to know about and/or fiddle with.&nbsp;&nbsp;Microsoft does a good job of passing out SDK info via their MSDN subscription.&nbsp;&nbsp;The Universal Subscription is very cool, albeit expensive ($2499 per user per year), because you get a copy of all their operating systems, development tools, and most productivity apps.&nbsp;&nbsp;Biggest problem with MS right now is the uncertainty with the results of the Justice Dept. sanctions.&nbsp;&nbsp;That, and their coming out with new development platforms every year or so that obsolete previous releases.<br><br>Chip H.<br><br>