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?
8  2536  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 TransferSpreads heet in help it appears you can link to the
spreadsheet and specify a range. That should work.
> Looking at TransferSpreads heet 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. Ja************@ ge.com wrote: Looking at TransferSpreads heet 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.
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.
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....Actual ly, 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.
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?
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.
=============== =============== ======= 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Andreas Lauffer |
last post by:
I changed from Access97 to AccessXP and I have immense performance
problems.
Details:
- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC
I used the SQL Profile to watch the T-SQL-Command which Access ( who
creates the commands?) creates and noticed:
|
by: Ootyguy |
last post by:
Trying to do this all day and googling for answers but found none, hope
someone can help. Thanks in advance.
select * into
OPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test)
from LocalTable
Reason: I am joining local tables with linked server tables using the
format "LinkedServer.database.owner.object" to execute a query, it
takes forever to execute since the tables joined on the remote servers
|
by: b b |
last post by:
I created the following code to delete all linked tables in my database
(Access 200):
--------------------------------------------------------
Dim tbl As TableDef
Dim dbs As Database
Set dbs = CurrentDb
For Each tbl In dbs.TableDefs
|
by: Neil Ginsberg |
last post by:
I have ODBC linked tables to a SQL 7 database in an A2K database. The linked
tables do not have the password stored in them, so the first time the user
accesses them, they need to enter the SQL password.
I am developing a process that will automatically run at night which will
access those tables. I need to be able to give Access the password, as the
user currently does, so that the process can run without a password prompt
appearing....
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For tables that involve a number
field as the primary key, the data is returned successfully. For tables
that involve a character field (e.g. CHAR(3) or VARCHAR(10)) as the primary
key, I have the correct number of rows returned, but the data displayed...
| | |
by: Jill Elaine |
last post by:
I am building an Access 2002 frontend with linked tables to an
encrypted Paradox 7 database. When I first create these linked tables,
I'm asked for the password to the encrypted Paradox database, and the
linked tables are successfully created. I use the data from these
linked tables in several forms.
All works great until I close the Access frontend and open it again.
When I try to use the forms, I get an error message: "Could not...
|
by: Janelle.Dunlap |
last post by:
I have a table in my database that is linked to an excel spreadsheet. I
need to be able to manipulate the data in this linked table so that I
can create smaller normalized tables that work with what I am trying to
do (all data used in my db will come from this one spreadsheet). I am
currently trying to utilize the make-table queries to create the tables
that I need from this data, however I am unsure of how to add foreign
keys to a...
|
by: Jim Devenish |
last post by:
I have a split front end/back end system. However I create a number of
local tables to carry out certain operations. There is a tendency for
the front end to bloat so I have set 'compact on close'
I think that I have read in some threads (althoug I cannot find them
now) that others place such tables in a local, linked database. I
could do this but I am interested to know what would be the advantages.
And disadvantages, if any.
Any...
|
by: salad |
last post by:
I have an A97 application that is NOT split on a network. It is used by
15+ folks continually. It is quick and fast.
I split it several years ago and had to merge it together again after
the folks rebelled at the slow speed resulting from the split.
I have to bite the bullet and split it now. Of course, it is much
slower. In one form that opens as a continous form, the data looks like
it is being repainted as it displays...you can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
