Splitting up a linked table into smaller tables

[Janelle.Dunlap]

My database is linked to external data from a single Excel spreadsheet.
I currently have it so that the entire spreadsheet exports into one
table, but really for the purpose of my database it will need to
somehow be split into smaller tables either before or after export.
The problem lies in the fact that I need to be able to do this all
programatically through vb code and still keep the links to the
spreadsheet. Any suggestions?

[salad]

Ja************@ge.com wrote:

My database is linked to external data from a single Excel spreadsheet.
I currently have it so that the entire spreadsheet exports into one
table, but really for the purpose of my database it will need to
somehow be split into smaller tables either before or after export.
The problem lies in the fact that I need to be able to do this all
programatically through vb code and still keep the links to the
spreadsheet. Any suggestions?

Looking at TransferSpreadsheet in help it appears you can link to the
spreadsheet and specify a range. That should work.

[Janelle.Dunlap]

> Looking at TransferSpreadsheet in help it appears you can link to the

spreadsheet and specify a range. That should work.

This won't work because I need to be able to import different
spreadsheets into the database that will be of different lengths. So I
can't just simply specify a range. Thanks for the suggestion though.
What I have been working on is creating different make-table queries
that essentially take the imported/linked data from the spreadsheet and
create smaller tables. The only problem I am running accross with this
is getting these new tables linked to the imported table so that when
the spreadsheet updates these smaller tables will update as well.

[salad]

Ja************@ge.com wrote:

Looking at TransferSpreadsheet in help it appears you can link to the
spreadsheet and specify a range. That should work.

This won't work because I need to be able to import different
spreadsheets into the database that will be of different lengths. So I
can't just simply specify a range. Thanks for the suggestion though.
What I have been working on is creating different make-table queries
that essentially take the imported/linked data from the spreadsheet and
create smaller tables. The only problem I am running accross with this
is getting these new tables linked to the imported table so that when
the spreadsheet updates these smaller tables will update as well.

Can you code? The reason I ask is that you might need to update via
automation. Go to
http://www.mvps.org/access/_vti_bin/...exe/search.htm and in the
search box enter
Excel
and check out some of the articles there.

I would also go to http://groups.google.com/advanced_search?hl=en and enter
*access*
in the Groups box and enter something like
Excel automation
in the WithAllWords box and see if other threads have some solutions you
can incorporate.

[Janelle.Dunlap]

I'll look into this...I appreciate your help. Maybe it's just me but I
think the problem I am having is not with Excel automation, its with
Access automation. My Excel spreadsheet links perfectly with my table
in Access...my problem lies in having the make-table queries I created
link to the table that was imported from excel. Currently when I make
a change to the spreasheet it updates this imported table but it
doesn't update the smaller tables that I created based on the imported
table. Hopefully I make sense.

[salad]

Ja************@ge.com wrote:

I'll look into this...I appreciate your help. Maybe it's just me but I
think the problem I am having is not with Excel automation, its with
Access automation. My Excel spreadsheet links perfectly with my table
in Access...my problem lies in having the make-table queries I created
link to the table that was imported from excel. Currently when I make
a change to the spreasheet it updates this imported table but it
doesn't update the smaller tables that I created based on the imported
table. Hopefully I make sense.

Yes it does. You have simply added complexity to the issue. You may
find that the whole process turns out nothing more than a headache. If
you update a smaller table that has no "real" association with the
linked spreadsheet, you'll need to do something to update it.

Hmmmm....Actually, all you may need is using OpenRecordset on the linked
Excel sheet and use FindFirst, Edit, Update, Delete, and AddNew to
modify it.

Also, You simply might need queries. Use queries to extract data from
the linked Excel sheet. That's probably the best option. If you can
program, you should look at the Connect property. This way you can
change the Connection (link) to Excel sheets on the fly.

[Janelle.Dunlap]

I'm fairly new to coding, but I didn't think it was possible to create
queries based on a spreadsheet. I thought it had to based on a table
or existing query. If I can create a make-table query directly from my
spreadsheet data that would solve the problem. Do you mind explaining
how this works?

[Ron2006]

In the relationship between Excel and Access

In Access
you may import from excel
You may export to excel
You may, thru automation (and vba code). open excel and change data.

You may also link to excell

BUT then you have the following to contend with:
========================
Microsoft recently mutilated the ability to change Excel data from
Access.

See:
Cannot change, add, or delete data in linked Excel tables in Access
2002
or 2003
at:
http://support.microsoft.com/kb/904953/
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
=====================================

[pietlinden]

Ja************@ge.com wrote:

I'm fairly new to coding, but I didn't think it was possible to create
queries based on a spreadsheet. I thought it had to based on a table
or existing query. If I can create a make-table query directly from my
spreadsheet data that would solve the problem. Do you mind explaining
how this works?

FWIW, you can link to the spreadsheet(s) and then just use make table
and append queries to create a normalized database. There's an example
of this in Roger Jennings's book (It's a Que book). he does it in the
first few chapters, if I remember right. You might want to read the
chapter. he does a really good job of explaining (and walking you
through) normalization.