dialog box to link table

  • Thread starter Thread starter Daryl
  • Start date Start date
D

Daryl

I am creating an access database named "stale dated checks" and when the
user opens the database I would like a dialog box to come up and ask which
Excel spreadsheet the user would like to use as linked table. I would like
the linked table to be called "tblOriginal". I am new to Access and VBA so
any help you can give would be much appreciated!
 
Daryl said:
I am creating an access database named "stale dated checks" and when the
user opens the database I would like a dialog box to come up and ask which
Excel spreadsheet the user would like to use as linked table. I would like
the linked table to be called "tblOriginal". I am new to Access and VBA so
any help you can give would be much appreciated!
Click to expand...


If you manually link to an Excel sheet, you can use a little
code to examine the linked table's properties to see how
it's done.

Debug.Print CurrentDb.TableDefs!tblOriginal.Connect

Somewhere in the connect string, you'll see the part with
DATABASE=path\file to the workbook

Debug.Print CurrentDb.TableDefs!tblOriginal.SourceTableName

will display the sheet or named range that you linked to.

With that information, you can then see how to construct the
strings that you need to set these properties in a VBA
procedure.

The code at: http://www.mvps.org/access/api/api0001.htm
can be used to let the user browse to the desired workbook.
I don't have any idea how you plan on selecting a specific
sheet or named range though.

Even though the final procedure will take a significant
amount of code, this should be sufficient to get you
started. If you need additional help, please ask a specific
question about a single aspect that's giving you trouble.
 
Back
Top