How to specify mdb as datasource?

[deko]

I want to connect to another mdb from an existing mbd - very simple. (same
local disk, same version)

when I use this convention:

Dim wrkJet As DAO.Workspace
Dim db as DAO.Database
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase(c:\database\my.mbd, True)

it works great. I've discovered if I use this convention:

Set db = wrkJet.OpenDatabase(varV, True)

I get a "Select Data Source" window in which I can browse for a data
source - this is *great*, but I can't seem to figure out how to use this
feature. If I browse to and select "c:\database\my.mbd" and click ok, it
creates something called "my.mdb.dsn" that doesn't seem to do anything. Is
this a syntax issue? How can I use the "Select Data Source" window to
connect to another mdb?

Here is more complete code:

Public Sub Maintenance
Dim db, dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim varV As Variant
Dim wrkJet As DAO.Workspace
Set dbs = CurrentDb
dbs.TableDefs.Refresh
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase(varV, True) <<== Here is where I get
"Select Data Source" window
[code omitted]
End Sub

[Pieter Linden]

"deko" <dj****@hotmail.com> wrote in message news:<eN*****************@newssvr27.news.prodigy.c om>...

I want to connect to another mdb from an existing mbd - very simple. (same
local disk, same version)

when I use this convention:

Dim wrkJet As DAO.Workspace
Dim db as DAO.Database
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase(c:\database\my.mbd, True)

it works great. I've discovered if I use this convention:

Set db = wrkJet.OpenDatabase(varV, True)

I get a "Select Data Source" window in which I can browse for a data
source - this is *great*, but I can't seem to figure out how to use this
feature. If I browse to and select "c:\database\my.mbd" and click ok, it
creates something called "my.mdb.dsn" that doesn't seem to do anything. Is
this a syntax issue? How can I use the "Select Data Source" window to
connect to another mdb?

Here is more complete code:

Public Sub Maintenance
Dim db, dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim varV As Variant
Dim wrkJet As DAO.Workspace
Set dbs = CurrentDb
dbs.TableDefs.Refresh
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase(varV, True) <<== Here is where I get
"Select Data Source" window
[code omitted]
End Sub

If you read the code, varV never has a value. If you want the user to
be able to browse for a file and assign it to that variable, then
you'd have to do a couple of things:
1. store the variable value in a table or custom database property
2. prompt the user to look for the file. (There's some really handy
code to do this here...
http://www.mvps.org/access/api/api0001.htm

You'd just assign the return value to a string, and then store it
somewhere. Okay, so how do you know the record exists? You query the
table it should be in, and if the recordcount=0 or the open fails,
then you prompt the user to find the file for you...

HTH,
Pieter

[deko]

Hi and thanks for the reply.

I understand that varV is missing - Windows figures this out and thus asks
for the missing Data Source in the "Select Data Source" dialog. I should be
able to use that dialog to get the right datasource. I've never used this
dialog before, and I'm not use what syntax to use, or what parameters are
required, or what constraints exist. While I could write code to get varV
(using the Common Dialog), I would much rather use the built-in "Select Data
Source" dialog - so my question is really about using that dialog and
understanding how Access recognizes and connects to different DSNs. For
example: How do I specify an Access mdb as a DSN name? What's the
difference between a File Data Source and a Machine Data Source?

Sorry if I wasn't clear in my first post....

[Pink Panther]

Hi Deko,

You can't link in another mdb file using ODBC (at least in 97)

What the various DSN's are, are explained in the 'select DSN' dialog.

Machine dsns
user dsn - local to machine only user can use
system dsn - local, all users can use, as well as remote services.

file dsn
creates a .dsn file to use

As someone else said in the thread - the best way is to use the code
from the access web to get the user to locate the mdb file, loop
through the linked tables and set the .connect and then .refreshlink

Peter

"deko" <dj****@hotmail.com> wrote in message news:<%b****************@newssvr29.news.prodigy.co m>...

Hi and thanks for the reply.

I understand that varV is missing - Windows figures this out and thus asks
for the missing Data Source in the "Select Data Source" dialog. I should be
able to use that dialog to get the right datasource. I've never used this
dialog before, and I'm not use what syntax to use, or what parameters are
required, or what constraints exist. While I could write code to get varV
(using the Common Dialog), I would much rather use the built-in "Select Data
Source" dialog - so my question is really about using that dialog and
understanding how Access recognizes and connects to different DSNs. For
example: How do I specify an Access mdb as a DSN name? What's the
difference between a File Data Source and a Machine Data Source?

Sorry if I wasn't clear in my first post....

[deko]

> You can't link in another mdb file using ODBC (at least in 97)

10-4

I did look at that code on the Access web - I think that may contain more
functionality than I need...

I've been using this:

Private Sub FindMdb() 'requires reference to Microsoft Office 11.0 Object
Library
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = False
.Title = " Select Access Database"
.Filters.Clear
.Filters.Add "Microsoft Office Access Databases", "*.mdb"
End With
If fDialog.Show = True Then
For Each varFile In fDialog.SelectedItems
If Len(varFile) > 0 Then
varV = varFile
Else
Exit Sub
End If
Next
End If
End Sub

Thanks again for the help!