launch .mdb file and open table

[RB Smissaert]

Given I created/updated a .mdb file like with:

Dim db As DAO.Database

Set db = DBEngine.CreateDatabase(strMDBPath, dbLangGeneral)
or
Set db = DBEngine.OpenDatabase(strMDBPath)

How do I launch this .mdb file and open a specified table?
I could do it with Shell I suppose (not sure about the table though), but
given
the above code there might be a better way.

RBS

 

[Guest]

It depends in what way you want to 'open' the table. Having returned a
reference to the external database with the OpenDatabase methods you can
return a reference to any of its tables via its TableDefs collection. You
can then establish a recordsdet using the OpenRecordset method and then
iterate through the recordset in code, search for a row in the recordset etc.

If, however, you simply want to open the table in datasheet view then this
won't do that. You could create a link to the external table in code using
the TransferDatabase method and then open the linked table. Another method
would be to create a temporary query with an IN clause which references the
external database and open that, e.g.

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strSQL As String
Dim strPath As String

strPath = "F:\SomeFolder\SomeSubFolder\SomeFile.mdb"
strSQL = "SELECT * FROM SomeTable IN """ & strPath & """"

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("Temp", strSQL)

DoCmd.OpenQuery "Temp"

dbs.QueryDefs.Delete "Temp"

Note that the path to the external file needs to be in quotes in the SQL
statement so when concatenating the strPath variable into the string
expression doubled quotes are used as these when used within a string
delimited by quotes evaluate to a quotes character.

Ken Sheridan
Stafford, England

 

[RB Smissaert]

Just want to do the as I would do manually:
double-click the .mdb
double-click the table, so it open in data view.

Tried your code, but get:
Object variable or With block variable not set (Error 91)
at the line:
Set qdf = dbs.CreateQueryDef("Temp", strSQL)

Can't I just automate Access and open the table with the Access methods?
Not sure why I have to run SQL if the table has been made already.
I am new to Access, so maybe I overlook some fundamental things.

RBS

 

[MH]

You may be going about this whole thing the wrong way, usually a database
(your .mdb file) is set up in advance with all the tables, queries, forms,
reports and code it needs to do the job already created. Could you give
some kind of overall view of what you're trying to achieve? Maybe someone
can come up with the "ideal" solution for you (obviously we'd leave all the
difficult stuff for you to figure out though!)

Regards

MH

 

[RB Smissaert]

OK, let me explain.
This is a database frontend, based on an Excel .xla add-in.
It connects to an Interbase database via ODBC.
The great majority of the reporting will be done in Excel,
but there is the option to output to Access.
So, the user runs a report from the .xla form and opts to output to an
Access .mdb file.
When the report is finished it is nice to directly launch that .mdb and also
open the
newly created table.
Now I can do the .mdb opening with the ShellExecute API and a simple
AppActivate.
It won't open the table though and that is the bit left to be done.
Hope this makes it all clear.

RBS

 

[Douglas J. Steele]

You shouldn't be opening tables anyhow. Create a form that displays the
data, and set that form as the Startup form (under Tools | Startup)

If you can't (or won't) do that, you can create a macro named AutoExec
that'll open the table.

 

[Dirk Goldgar]

OK, let me explain.
This is a database frontend, based on an Excel .xla add-in.
It connects to an Interbase database via ODBC.
The great majority of the reporting will be done in Excel,
but there is the option to output to Access.
So, the user runs a report from the .xla form and opts to output to an
Access .mdb file.
When the report is finished it is nice to directly launch that .mdb
and also open the
newly created table.
Now I can do the .mdb opening with the ShellExecute API and a simple
AppActivate.
It won't open the table though and that is the bit left to be done.
Hope this makes it all clear.

You can probably do it like this:

Dim appAccess As Object
Dim strDatabasePath As String
Dim strTableName As String

strDatabasePath = "C:\Your Path\YourDB.mdb"
strTableName = "YourTable"

Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strDatabasePath
appAccess.Visible = True
appAccess.DoCmd.OpenTable strTableName
Set appAccess = Nothing

 

[RB Smissaert]

Not sure what is wrong with opening a table.
All the users will want to do is see the data and maybe sort and/or filter.
These are newly made .mdb files as the result of a SQL query run from Excel.
So, if there has to be a form I will have to add extra code to create this
form everytime.
Still, I will have a look into this suggestion.

RBS

 

[RB Smissaert]

Thanks, that works.
Had tried it already, but for some reason it didn't work.
Must have done something slightly different.
This does the trick.

RBS

 

[Douglas J. Steele]

Tables are meant for storing data, not for displaying the data, and
certainly not for updating the data.

 

[RB Smissaert]

Sure, I understand what you are saying, but this is just
for a quick display. It is not meant to be a worked-out application.
Updating the data won't happen. The users of this won't probably
know how to do it.
Sole purpose of the whole thing is to see data that can't be shown in
Excel due to having more than 65535 rows.

Considering all of the above, what would be the advantage in showing
the data in a form?

RBS

 

[MH]

Glad you finally got it all sorted out RB.

One option you may want to give your users is to select an existing MDB file
and the table name to export to, it may be better to store the output from
different queries in one mdb file. Just a suggestion of course.

MH

 

[RB Smissaert]

That is exactly what I am doing already.
Option to make new or use existing .mdb.
Option to name the table.
All working nicely.

All this is just a little side issue to have a go with Access and offer the
users some other option.
The main thing will be to re-write a large part of my application and do all
the data manipulations
in Access, rather than arrays and SQL on text files. All doable, although I
can see it will be a big job.

RBS

 

[RB Smissaert]

That is exactly what I am doing already.
Option to make new or use existing .mdb.
Option to name the table.
All working nicely.

All this is just a little side issue to have a go with Access and offer the
users some other option.
The main thing will be to re-write a large part of my application and do all
the data manipulations
in Access, rather than arrays and SQL on text files. All doable, although I
can see it will be a big job.

RBS

 

[MH]

I'm sure you'll cope! <g>

MH

That is exactly what I am doing already.
Option to make new or use existing .mdb.
Option to name the table.
All working nicely.

All this is just a little side issue to have a go with Access and offer
the users some other option.
The main thing will be to re-write a large part of my application and do
all the data manipulations
in Access, rather than arrays and SQL on text files. All doable, although
I can see it will be a big job.

RBS

 

[MH]

I'm sure you'll cope! <g>

MH

That is exactly what I am doing already.
Option to make new or use existing .mdb.
Option to name the table.
All working nicely.

All this is just a little side issue to have a go with Access and offer
the users some other option.
The main thing will be to re-write a large part of my application and do
all the data manipulations
in Access, rather than arrays and SQL on text files. All doable, although
I can see it will be a big job.

RBS

 

[RB Smissaert]

I would for sure if it was my day job.

RBS

I'm sure you'll cope! <g>

MH

 

[RB Smissaert]

I would for sure if it was my day job.

RBS

I'm sure you'll cope! <g>

MH

 

[Pieter Wijnen]

The Way to open the table directly is to create a macro called AutoExec &
Add a OpenTable Command

HTH

Pieter

 

[Pieter Wijnen]

The Way to open the table directly is to create a macro called AutoExec &
Add a OpenTable Command

HTH

Pieter