Question HELP! Is it Access or Jet

[kdefilip]

Hi. I have been looking into a process that is confusing me.
Server A launches a MS Word MailMerge Doc which contains a DSN to a mapped drive on Server B

Server B has a copy of an Access DB at the mapped drive location.

As I watch this process on both servers, never do I see a process started for MSAccess.

What is happening here? Is the MSWord doc simply querying the MDB file directly on the fileshare using the Jet engine on the fileshare.

How does this process work?

Thanks for any insight you may offer or links.

 

[Banana]

I dont' see anything wrong with that.

Jet is the database engine of Access. Access is the interface and IDE that allow you to interact with Jet. Since you're connecting to the Jet using DSN, there's no need for Access to be involved.

 

[kdefilip]

Hi
I assumed that was the case, but do you have any way to verify that, a link at msdn/MS. I need to confirm this to a reluctant audience.

 

[Banana]

Sorry, don't know of any links. The best thing you could do is link to a document from MS stating that Jet is the database engine and the Access is the interface (which there's plenty documents to choose from) then show them that the DSN refers Jet provider, not a Access provider, so that's reasonable evidence.

If they can't accept that, then.... what is their problem?

BTW- I'd bet that if you got your hand on a copy of standalone Jet engine (it was bundled in other software bundles... probably VB6 stuff), it'd work just the same!

 

[kdefilip]

Yes, I will try to find some docs.
The DSN looks like this - which is a little foreign to me.
"DSN=MS Access Database;DBQ=" & strDocumentPath & "Access_Database\" & "myfile.MDB;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:="SELECT * FROM `MyTable`", SQLStatement1:=""

 

[Banana]

Oh, that's not what I was expecting.

I was expecting something like:

"DSN=Jet 4.0 OLE DB Provider" or something like that.... If you go to Carl Prothman's connection string, he probably has the connection strings for what you need. (Be to keep in mind that Jet 4.0 is only available for certain version; I think 97 uses 3.5 while 2000 and after uses 4.0?)

 

[kdefilip]

Yes, this is access 2002 using Jet 4.0. However, I believe there may be issues with this DSN string, but I don't know enough about it to be sure. It looks like they are calling an odbc driver (file 15??), but this is sure a weird way to go about it.

 

[Banana]

Ah, they're probably doing it the old way.

The new way, (Mind, I haven't personally done it!) is to use OLE DB provider, which should be included in the Jet 4.0.

If you go to the Carl's site, you should get more information. Googling should get you pleothera information on how to set up the DSN using the Jet's OLE DB provider. In fact, I'm quite sure there's a KB article somewhere at MSDN...

 

[kdefilip]

Yes, I'm there now. Looks like they should be using a DSN-less connection. I can tell by the timeout=5, that this was created during an older version of MDAC/Jet as the timeout changed from minutes in <2.0 to milliseconds in => 2.0 - clearly this is a problem, but some of the other stuff in the DSN string is baffeling me. DriverID and FIL - I have never seen nor can I find reference to it anywhere.
Thanks again.

 

[Banana]

If you want, it may be simpler to just save the old DSN and try anew with clean DSN... (or DSN less connection, if you don't mind initializing this with code)

 

[kdefilip]

Yes, I agree. It may be the way to go. However, I don't know enough about connection strings, especailly ones that are being passed to a MS Word mail merge doc. I'll have to keep looking and see if I can find docs on it somewhere.
Thanks.

 

[datAdrenaline]

I just want to add ... you asked ...

>> Is the MSWord doc simply querying the MDB file directly on the fileshare using the Jet engine on the fileshare. <<

And Bannana has explained that JET is a Database Engine that is INDEPENDANT of Microsoft Access. JET was actually part the MDAC for some time, so you got JET with Windows XP. But it is important to note that when you are asking and receiving data the JET engine is working on the machine that is doing the asking and receiving, the server that stores the data does absolutely no work toward the data request what so ever. Server B is merely a disk holding a file.

Also ... in "proper" terminology, the correct way to say this:

>> Server B has a copy of an Access DB at the mapped drive location <<

Would be ...

Server B has a JET formated database file that can be accessed through a mapped drive letter.

The KEY is that it is not really an Access database (although an .MDB is even refereced as an Access Database in MS documentation!!), its a JET formated database .... Access merely utilizes the JET format to store its objects and data (by default at least).

Also of interest may be the information that Access does NOT interface DIRECTLY with the JET database engine. Under the hood, as well as, in VBA Access uses an API (Application Programming Interface). Access really really really likes to use DAO (Data Access Objects). DAO is the object model that Access Objects (forms, reports, queries, and tables) use when manipulating data (even in A2000 where ADO was the default object model for VBA!!). Also, Access can use ADO (ActiveX Data Objects) form and reports when forced to . In VBA both object models can be utilized extensively to interact with the JET database engine. Note that when data is manipulated VIA ADO or DAO objects in VBA, the Access environment is merely providing the enviroment for the code and is not doing anything with respect to data requesting or receiving! ... interesting eh?....

So the path for accessing data stored in a JET formated datafile is ...

An Application (ie: VBA, Word, Excel, Access) works with an API (DAO/ADO); the API works with a "Data Provider" (OLE DB, DAO); the data provider communicates with the DB engine; the DB engine fulfills the request and returns the result to the data provider, then the data goes back up the path. I know there are many books the lay out this topography ... I know that chapter 17 (the first of the four chapters regarding ADO and DAO that I wrote for this book) details some of this out for the reader.

-----

Here are some articles that may be of interest (I will post more if I can find some ... I found a bunch while writing those chapters!):

http://blogs.msdn.com/data/archive/2006/12/05/data-access-api-of-the-day-part-i.aspx
http://msdn.microsoft.com/en-us/library/ms722791.aspx
http://support.microsoft.com/kb/275561

....
EDITS ADDED >>>
WOW! .... lots of conversation during the composition of my post! ... Hopefully the info is still helpful.

 

[kdefilip]

Yes, very helpful. If you have a moment more, could you look at the DSN string they are using and first tell me if you see something wrong other than the timeout of 5. And also if you would suggest a better method or string to accomplish what they are doing? I notice they are using fileid which I assume is the fileid of the odbc driver they are trying to use. This string is passed to word through a vb script. However, there are 3 potential "application" server from which this process is started. I assume there is a potential for the odbc file id to be different on each machine.
Anyway, any advice you can offer I would appreciate.
Thanks for the links. I will review in a moment.
Thanks again.

One more thing, so the jet version and the mdac version are irrevelent on the file server; is that correct?
thanks.

 

[Banana]

Two things...

I want to make sure... is this DSN used in Word environment, not Access?

As for MDAC version, I believe it stopped around 2.7 or something like that but is in fact a set of several components, of which Jet is one of them, and respective components may have their own verisoning. I wouldn't bet on using a older MDAC with newer Jet, though...

 

[kdefilip]

Well, the process goes like this. A vb script starts and passes the dsn to a word merge doc. The word merge doc, using the dsn, queries an access mdb file over a mapped drive to a file server. The mdb file has linked tables back to some flat files on another system through odbc. However, for this part of it, it is basically the interaction between the word merge file and the access/jet datafile.

However, I suppose that each time the datafile is accessed(opened) there must be some interaction from the access file to the linked tables.
Thanks.

 

[Banana]

Not really. Even if there are linked tables within the database, it's still Jet's job to manage the carting of data, even from a linked source. IINM, the only time Access is involved is whenever you use a library database, a module, a add-in, but that is quite very rare.

 

[datAdrenaline]

Quick comment ... a DSN is a series of settings stored in a file or registry keys. The "DSN" itself is not refered to as a "string" simply because its really not ... its more like a file ... so ... when your "Connection String" refers to a "DSN", the application will see the DSN reference, then "Open" that file (or set of registry keys) and get some connection settings from the DSN file ...

When I look at the contents of one of my file based DSN's (DSN stands for Data Source Name), which are simply text files with a .dsn extension .. here is what I see:

[SIZE=2][ODBC][/SIZE]
[SIZE=2]DRIVER=Microsoft Access Driver (*.mdb)[/SIZE]
[SIZE=2]UID=admin[/SIZE]
[SIZE=2]UserCommitSync=Yes[/SIZE]
[SIZE=2]Threads=3[/SIZE]
[SIZE=2]SafeTransactions=0[/SIZE]
[SIZE=2]PageTimeout=5[/SIZE]
[SIZE=2]MaxScanRows=8[/SIZE]
[SIZE=2]MaxBufferSize=2048[/SIZE]
[SIZE=2]FIL=MS Access[/SIZE]
[SIZE=2]DriverId=25[/SIZE]
[SIZE=2]DefaultDir=<folder path>[/SIZE]
[SIZE=2]DBQ=<folder path>\<somefile.mdb>[/SIZE]

Its importantant to note that a DSN may provide ALL or just some of the settings needed for a successful connection to a data source, so in the case I presented, I beleive the only thing needed in the connection string property would be a reference to the DSN ...

DSN=<my dsn name>

... Or ... you can go DSNLess ... then the connection string would contain all the connection settings {delimited by a semi-colon ( ; )} required (note that some may be in a DSN, or connection string, but they are for information only and don't really assist in the connection process) ... so the above DSN would look like something like this as a raw connection string ...

ODBC;DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes ....

It may also be important to note that DSN's (I beleive) are ODBC based, which leads me into what bannana suggested earlier about using the OLE DB provider.

So if you are using WORD to connect to Access/JET ... my first question would be .... with what object model are you using to connect to Access/JET ... ADO? DAO? ... if you use the OLE DB provider, then you are definately going to use ADO. If you are using the ODBC (DSN) settings, you can use ADO or DAO.... But now, I will toss in the fact that if you reference the DAO object model in your Word VBA project (if you are using VBA in this process), the connection to the data becomes (in my opinion) very easy with the .OpenDatabase method of a DAO database object.

But ... from what I recall, for Word Mail Merges, you mearly specify a DSN .... and word will use what it sees fit through the ODBC driver (my guess it that is uses DAO) ... so ... I guess this leads us to this ... if your DSN is "Complete", than all you have to do is pass that dsn name (with OUT the extension or path) {similar to what I have shown above} to the Word mail merge document, then word will use that DSN to properly connect to the data source ...

... Ok ... maybe this wasn't the "Quick Comment" I thought it was going to be ...

EDITS ADDED >>>

Even though the DRIVER is specified as Microsoft Access Driver (*.mdb) ... its important to note that JET is still the manipulator of the data (just as Bannana has indicated) and the MS Access application is not needed at all. The integration of JET/Access and even DAO was so tight "in the beginning" (like back in Access v1.0) that as the distinctiveness of each grew over time, the terminology and synonomous use of the terms (specifically "JET database" and "Access Database") has persisted to this day. Now with ACE (JETs successor) being coined as the "Database Engine for Office" and officially tagged as "Access Connectivity Engine" entity distinction is still sometimes difficult to discern.