Linking Queries To Excel File

[Tamer]

Hi all,
I'm trying to link 3 Access queries to 3 worksheets in an
Excel file. I used the following code in an event click in
a form, the code doesn't return any error, but it doesn't
establish the link as well. What am I missing?
Thanks in advance.

Dim strFileSpec As String
strFileSpec = "M:\Excel Files\SusanFiles.xls"

DoCmd.TransferSpreadsheet acLink, _
acSpreadsheetTypeExcel9,"qry_SuzanFiles_Adoption", _
strFileSpec, True, "Sheet1!"

DoCmd.TransferSpreadsheet acLink, _
acSpreadsheetTypeExcel9, "qry_SuzanFiles_Discharged+Age",_
strFileSpec, True, "Sheet2!"

DoCmd.TransferSpreadsheet acLink,
acSpreadsheetTypeExcel9, "qry_SuzanFiles_FP", _
strFileSpec, True, "Sheet3!"

 

[M.L. Sco Scofield]

TransferSpreadsheet does just that, it transfers data to/from a spreadsheet.

If you're trying to create a link, you'll need to use some DAO code. You want to look for information on linking tables which also
applies to linking to other data sources like Excel. Start with the TableDef Object and it's Connection property.

There used to be some excellent examples in the 97 help file. I'm not at a machine with 2002 or 2003, so I can't tell you if they're
there anymore.

BTW, this group is for general questions. For this kind of question, the microsoft.public.access.modulesdaovba is the perfect group.

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[M.L. Sco Scofield]

Well Tamer, first I have to apologize for a gap in my knowledge. Although I
always use the method I mentioned in my first message, you can, (as you
thought,) use TransferSpreadsheet to link spread sheets. This is what
happens when you get stuck in a rut and do the same thing all of the time.
Sorry about that.

Your code is just fine. You don't get any error messages because there isn't
any. After rereading your message, I think I figured out your problem.

When you use the acLink option, TransferSpreadsheet creates a link to the
spread sheet. A link looks and acts like a table and shows up with your
tables. It does not link the spreadsheet to a query.

After you have a link, you can use the link just like a table in your query.

If you look at your list of tables, you will probably see the links with an
XL icon and the name of your query with a 1 at the end. I.E.,
qry_SuzanFiles_Adoption1. This is because queries and tables share the same
name space and you can't have a query and a table with the same name.
Instead of giving an error, Access just tacks on the 1.

Sorry again for not realizing TransferSpreadsheet could be used for linking.

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

TransferSpreadsheet does just that, it transfers data to/from a spreadsheet.

If you're trying to create a link, you'll need to use some DAO code. You

want to look for information on linking tables which also

applies to linking to other data sources like Excel. Start with the

TableDef Object and it's Connection property.

There used to be some excellent examples in the 97 help file. I'm not at a

machine with 2002 or 2003, so I can't tell you if they're

there anymore.

BTW, this group is for general questions. For this kind of question, the

microsoft.public.access.modulesdaovba is the perfect group.

 

[M.L. Sco Scofield]

Well Tamer, first I have to apologize for a gap in my knowledge. Although I
always use the method I mentioned in my first message, you can, (as you
thought,) use TransferSpreadsheet to link spread sheets. This is what
happens when you get stuck in a rut and do the same thing all of the time.
Sorry about that.

Your code is just fine. You don't get any error messages because there isn't
any. After rereading your message, I think I figured out your problem.

When you use the acLink option, TransferSpreadsheet creates a link to the
spread sheet. A link looks and acts like a table and shows up with your
tables. It does not link the spreadsheet to a query.

After you have a link, you can use the link just like a table in your query.

If you look at your list of tables, you will probably see the links with an
XL icon and the name of your query with a 1 at the end. I.E.,
qry_SuzanFiles_Adoption1. This is because queries and tables share the same
name space and you can't have a query and a table with the same name.
Instead of giving an error, Access just tacks on the 1.

Sorry again for not realizing TransferSpreadsheet could be used for linking.

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com