[RESOLVED] How Does VB I Want to Use MSAccess

[dw85745]

I'm trying to create multiple instances to MSAccess.
In doing so it raised the above question of "How Does VB Know I Want to Use MSAccess?".

--------------------------
I currently use Access by:

   Public Declaration
   Public DaoDb As DAO.Database
   Public accApp As DAO.Database      '<<Ignore this for now

   'Procedure Code of Interest
  'Get DaoDb location, name, and open it
  'This returns an Access *mdb file
   strPath = MCommon.GetPath(PATH_DB)
   
   'Open Database
   '-- DaoDB defined globally
   '-- Parameters  (There are other options not used)
   '   -- strPath of DB
   '   -- How Opened {True = Exclusive, False = Shared}
   '   -- Access Method, {False = Read-Write, True = ReadOnly})
   'Opening in Exclusive Mode does NOT allow other instances
   'of VB to gain access to the database
   Set DaoDb = OpenDatabase(strPath)   ', True, False

Access is NOT listed as a Reference in Project References


so How does VB know I want to use MsAccess?

//////////////////////////////

To further confuse myself, after running the above code and looping the processes in task manager,
Access does not even show up as a running process.

What gives???

[dilettante]

Because DAO only works with Jet databases (not "Access"). Anything else requires RDO (another obsolete technology), ODBC Direct (bleh), or ADO which is what you really should use except in rare cases.

[dw85745]

Thanks for input.

Logically (if I can rely on my logic??)

It makes since that if the OS on my machine defines *.mdb as being used by an Access database that somewhere there is information stored and accessible that
anything with *.mdb is an Access mdb.

As you point out, it further makes sense that since I am referencing Microsoft DAO 3.51 object library (Instead of Java DAO or Oracle DAO) that somewhere within
DAO it must reference (default to) Jet databases -- however MS defines them (e.g. is Access, MySQL, or SQL Server all considered Jet)?

I found this on Wikipedia:

There are three modules to Jet: One is the Native Jet ISAM Driver, a dynamic link library (DLL) that can directly manipulate Microsoft Access database files (MDB) using Indexed Sequential Access Method (ISAM). Another one of the modules contains the ISAM Drivers, DLLs that allow access to a variety of ISAM databases, among them Xbase, Paradox, Btrieve and FoxPro, depending on the version of Jet. The final module is the Data Access Objects (DAO) DLL. DAO provides an API that allows programmers to access JET databases using any programming language.

So logically then (??) if I am manipulating an *mdb file (really an ISAM file??) using DAO, I guess in affect that that file (*.mdb) is really being manipulated WITHOUT ever opening MSAccess
(which is really a separate program and GUI ??) .

So if the above is correct, why can't I open two different *.mdb files using DAO and then copy records between them (see Database forum >> http://www.vbforums.com/showthread.p...ss-Databases)?

[DataMiser]

You should be using ADO which will allow you to easily work with any of those databases. DAO is way past its prime and should not be considered.

As for *.mdb being considered an Access database, the OS does not define this, it is only true if MS Access is installed on the system. [possibly there could be other programs that would add the entry]
All it is is an entry in the registry for file associations so that when you click on the file Windows knows what program to use. MS Access adds an entry for mdb files

No MySql is MySQL and SQL Server is SQL Server and ORacle is Oracle all are ODBC.

You can work with Access databases without MS Access being installed on the system. The drivers work directly with the data file, but again as mentioned in post #2 in all but rare cases you should be using ADO rather than DAO.

If you insist on using DAO it will be harder to get help as most people stopped using it over 10 years ago and even those of us who used it a lot will be very rusty and may not bother to try and remember. You will also be learning what is basically a dead technology that will serve little purpose other than make it harder to learn the ADO and ADO.Net methods.

[dw85745]

DataMiser

Not trying to be argumentative, and I understand your point, but so is VB Classic, VB ADO, as well as a lot of other languages (ALGOL, PL1, etc. to name a couple).
If you have a large DB to maintain you can't be jumping languages everytime M$ or any other business that has a foothold wants to change for the purpose of making $$$$$$$.
I for one am OLD enough to have gone through a number of languages and am just plain tired of the wheel being reinvented since the ONLY objective
is to get something into memory, manipulate it, and get it out again.

Per Wiki DAO is an integral part of Windows (like IE) and is supported through Window 7 (not sure about Win 8 and date of Wiki article).

Will keep hoping someone has the answer.

Using an MSDN example for VBA still errors.

Public Sub TransferFunds()
'FOR TEST
    Dim wrk As DAO.Workspace
    Dim dbC As DAO.Database
    Dim dbX As DAO.Database
    
    Set wrk = DBEngine(0)
    Set dbC = DaoDb        'CurrentDb
    Set dbX = wrk.OpenDatabase(GetPath(PATH_DBBackUp))
    
   Dim strSQL As String
    
    On Error GoTo trans_Err
    
    'Begin the transaction
    
    wrk.BeginTrans
    
    'Withdraw funds from one account table
'    dbC.Execute "INSERT INTO tblAccounts ( Amount, Txn, TxnDate ) SELECT -20, 'DEBIT', Date()", dbFailOnError
     strSQL = "INSERT INTO Restore2348_0 SELECT * "
     Debug.Print strSQL
     dbX.Execute strSQL, dbFailOnError


    'Deposit funds into another account table
'    dbX.Execute "INSERT INTO tblAccounts ( Amount, Txn, TxnDate ) SELECT 20, 'CREDIT', Date()", dbFailOnError
'     dbC.Execute "INSERT INTO Restore2348_0_H SELECT * ", dbFailOnError
     strSQL = "INSERT INTO Restore2348_0_H SELECT * "
     Debug.Print strSQL
     dbC.Execute strSQL, dbFailOnError

    
    'Commit the transaction
    wrk.CommitTrans dbForceOSFlush
    
trans_Exit:
    'Clean up
    wrk.Close
    Set dbC = Nothing
    Set dbX = Nothing
    Set wrk = Nothing
    Exit Sub
    
trans_Err:
    'Roll back the transaction
    wrk.Rollback
    Resume trans_Exit
    
End Sub

[dac999]

I use DAO. It still works fine. Here is some code that may work for you:

Public Sub TransferFunds()
Dim DB1 As Database
Dim DB2 As Database
Dim RS As Recordset
Set DB1 = OpenDatabase("Path1")
Set DB2 = OpenDatabase("Path2")
Set RS = DB1.OpenRecordset("tblAccounts")
With RS
.AddNew
!Amount = -20
!Txn = "Debit"
!TxnDate = Now
.Update
End With
Set RS = DB2.OpenRecordset("tblAccounts")
With RS
.AddNew
!Amount = 20
!Txn = "Credit"
!TxnDate = Now
.Update
End With
Set RS = Nothing
Set DB1 = Nothing
Set DB2 = Nothing
End Sub

[dw85745]

dac99:
Thanks for response. Will try your "old School" suggestion.
If that works (feedback later this PM on this) then SQL should *(%$# well work!!

[DataMiser]

Note that in a multiuser application Using rs.Addnew can lead to issues as well be it DAO or ADO.

If you know DAO and want to use it then that is an option in many cases but one that I would not recommend.
If you do not know DAO already then you should not bother with it and should focus on learning ADO instead.

In multiuser applications you will find that using SQL Insert and update statements are better than using the RS.AddNew and/or RS.Edit methods. Especially if there is going to be much traffic in and out of the DB

[dw85745]

dac999:

Just tried the "old school" method of:
1) Declaring a Recordset in each DB
2) Using ".AddNew" with each field name to move field values between each DB
3) Worked Great !!!!!!!

So the question is why the SQL does NOT work -- at a complete loss here unless something going on
behind the scenes with DAO and Microsoft SQL ?

----------------------------------

DataMiser:
Don't disagree with your last post.
dac999 offered a "Great" suggestion which in effect was to back up and see if you could just get it to work between Multiple DB.s
IMHO, we have now narrowed it down to "most likely" a issue with DAO and Microsoft SQL.

Have any suggestions as to why the SQL "INSERT INTO" is not working ??

[DataMiser]

Honestly I have never used DAO with SQL server so I am not sure. I think you would need to have the proper ODBC driver installed on the system and create a DSN.

I do know that if you use ADO it is simple and in almost every case better than DAO which is why ADO was added into VB6 rather than the DAO that was in VB5. DAO was still supported but is seldom used.

With ADO I have had code work with several Access DBs, SQL Server, MySql as well as others and for the most part it is as simple as changed the connection string.

Again if you know DAO and do not know ADO then it would make sense to go that route but if not then it makes no sense to learn a dead technology in favor of a better and more current option.

You can disagree with that if you like but that would only indicate that you do not know what you are talking about.

As far as why your code don't work with SQL server my guess would be it is the way you are trying to open the database. Access dbs you work with the file but not SQL server.

At any rate it has been at over10 years since I bothered with DAO as it did not take long to see that ADO was the better option.

[dw85745]

I have never used DAO with SQL server so I am not sure

Not sure where SQL Server was mentioned in this discussion -- not by me or dac999 as I read the posts.
I did refer to "Microsoft SQL" which is Microsoft's version of SQL and its usage in DAO.
From what I can put together so far, it appears Microsoft's version of SQL does NOT like INSERT INTO with a SELECT * (still trying it id where exactily the problem resides but leaning toward the * character).

Any valid input regarding a solution to the problem is greatly appreciated.
This post was Not designed for an DAO versus ADO debate. If you want to start a thread on that I'll be glad to chime in with My2Cents.
Here's a good place to go to debate the issue http://blogs.msdn.com/b/michkap/arch...3/3849288.aspx

I'm just trying to resolve a particular problem with an old program that had worked extremely well using DAO with an AccessDB.
Here is Microsoft take on Access (DAO versus ADO): http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

[dw85745]

Finally found a very old diagram which helps define Visual Basic and Access relationship to DAO.
This confirms "dileantte's" post.

So the *.mdb database is independent of Access and
any program (e.g. Access, Visual Basic, Excel, etc.) are manipulating the DB through
a reference to the DAO library which uses JET.

Referencing Access from Visual Basic in Project References only brings in
Access Objects. Access references "DB.Engine" but to use it DAO must be
separately referenced.

Would like to find an updated diagram for VB6 as well as one that shows the interrelationship
of ADO to *.mdb. Anyone have one please post.

[dilettante]

You have to remember that DAO was never a mainstream data access technology, and was always limited to VB3 through VB6 and Access (once the latecomer dragged itself in the door).

By the time VB4 rolled around the handwriting was already on the wall. DAO and the "ODBC band-aid" RDO just weren't going to be the future, and already were being relegated to the Access ghetto. VB began moving away about the time of VB4's 32-bit personality (VB4 was a bridge release supporting 16-bit and 32-bit applications).

By 1996 the ODBC replacement OLEDB gained the COM-oriented API ADO, so starting with VB4 this is where the thrust moved. DAO was still supported through VB6 in order to make porting old programs forward easier.

But since VB4 32-bit and 1996, ADO has really been the path forward. ADO saw further extensions up until the Great Mistake, also known as .Net.


Early in the history of .Net two things became clear: (1.) .Net garbage collection resulted in performance problems using wrapped COM libraries like ADO, and (2.) ADO programmers had been slow to grasp and adopt the "disconnected" model of data access introduced in ADO 2.5 - and to make .Net cope well next to Java the .Net programmers needed more than a small prod in this direction. So ADO.Net was born. Which you can ignore since it doesn't apply to VB6 anyway.


You may find this article useful: Microsoft Data Development Technologies: Past, Present, and Future. While it intentionally marginalizes VB6 (being written for .Net users) the basic chronology is correct and the diagrams might be useful.

Thus while the ODBC, OLE DB, and ADO technologies continue to age, they remain the primary data access technologies for unmanaged code (i.e. Win32) applications. Today they are collectively known as the Microsoft Data Access Components (MDAC) or the Windows Data Access Components (WDAC), and are part of the Windows SDK.

[Bobbles]

You don't want a debate on ADO vs DAO as you have a large program that is heavily using DAO, and you don't wish to rewrite it all (using ADO).
We cannot argue with you on taking that position (PERHAPS you should have mentioned that at the beginning ?)
Did you know that a project will run merrily if you have both DAO and ADO both being used ?
I had a very large project, which I wished to upgrade to ADO (And it is the way to go).
What I did was rewrite individual Forms (using ADO), whenever they needed enhancing or fixing. Over a period of time the whole project ended up using ADO (the way God intended).

Mind you I did not have any bad habits in my Project, like maintaining a connection to a DB. If your project maintains long connections, I am not sure if my 'change it over months' approach will work.

[dilettante]

The original question was something on the order of "I'm using an Access database, how do it know?"

[SamOscarBrown]

@DataMiser: May I use this quote?....LOVED it:

"You can disagree with that if you like but that would only indicate that you do not know what you are talking about."

(and not to get into discussion w/DAO/ADO---just thought this quote might fit in with some co-workers...!) :-)

[dw85745]

OK, Pile On Guys!!!!!!!!

dilettante
You may find this article useful: Microsoft Data Development Technologies: Past, Present, and Future. While it intentionally marginalizes VB6 (being written for .Net users) the basic chronology is correct and the diagrams might be useful.

Thanks for the link

Bobbles
Did you know that a project will run merrily if you have both DAO and ADO both being used ?

Yes, well aware of it.

/////////////////////////////////

Deprecated software (because of corporate desire to make more $$$) IMHO is one of the biggest problems going.
Do you keep maintaining the original code, or try and rewrite using whoever says is the latest and the greatest.
COBOL worked great, and there are still a lot of COBOL programs running.
Even C++ (which Microsoft has deprecated) is still being updated by Bjarne Stroustrup, and from my understanding is still the language of choice by Microsoft to write Windows OS -- go figure.
VB6 is deprecated, so I guess we all switch to NET and close the VB Classic forum.
What a shame. IMHO VB6 + API allowed you to do most anything, and quickly.
As you become an old fart like myself, and have been through several of these "newest and greatest", you finally finally reach a point of %^&$# it, I just keep doing what I was doing.

[dilettante]

DAO is a funny case though. It really is a relic of the short-lived Win-16 era, while ADO mostly replaced it by 1996.

That doesn't mean DAO is useless by any means. But it has a narrow range of use, and programs constructed around it can be a lot of work to move from Jet MDBs to clent-server DBMSs and even a chore to move to something like Classic ASP (or script in general) where DAO isn't available.

DAO wasn't really meant to be used in new programs after VB5. It is in VB6 only to assist in porting older programs forward. Imagine if they'd removed it?

C++ isn't deprecated. You can still get in VS 2012.

[Bobbles]

I am probably an older fart than you (>70)
I am sticking with XP
I am sticking with the pre Ribbon Office
I am sticking with VB6
I am not sticking with DAO

[dw85745]

dilettante: Thanks for the input; I found this link most informative >> http://msdn.microsoft.com/en-us/library/ee730343.aspx

Bobbles:

You probably then remember the old keypunches(w/o the print on top), collators, edline, and Revelation before DB2. Nice to reminisce sometimes.