473,887 Members | 2,315 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Export to Excel, update by other User and Import back to Access

1 New Member
Dear all,

I have an enormous database (Access 2003) containing sales information, and an Excel tool to enable end users to do planning and forecasting. Untill now I would create several queries, export the data to Excel (Excel 2003) and store the Data in the tool for the users to work with. I always have to export all information for a group of users as I never know on what part of the data they want to work. This makes the Excel files very slow however (to much data, in general over 40.000 lines).
I know there is a possibility to import data in Excel from Access and you can refresh the data in Excel with new information in Access. So possibly I can use this, by having the end users run a query that will only pull the data they need at the time. The only thing is, the changes they make will then also have to be uploaded/ refreshed in Access too. Unfortunately my end users do not have a clue about Access and very limited knowledge about Excel. So all will have to be put in an VBA macro so they can action things with one click of a button.
Any clues from your side will be highly appreciated!

Thanks,
Pauline
Sep 17 '07 #1
1 2328
nico5038
3,080 Recognized Expert Specialist
Hi Pauline,

Guess this can't be solved in an easy way when you want a 100% solution.
Your present application solution will have trouble when multiple users update the same information (ID) with different data....
It would be best to move all tooling of Excel into Access and have the users work with an Access frontend.
The other solution should be to split the original data into "unique" sets for Excel per user and thus prevent duplicate updates.

The last solution will be the easiest, but also requires the users to be arranged to handle one of the sections. When multiple users have to manage the same information, they'll have to assign one that's responsible for the final dataentry.

When you can divide the data in this way, then all needed will be to add a field ResponsibleUser to every unique datarecord and split the data into sheets per user.

How does that look like ?

Nic;o)
Oct 5 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
2473
by: Ivan Zuzak | last post by:
Hello. Sorry for crossposting, but i didn't have a better idea as to where to post the question. I'm using certificates to sign and encrypt SOAP messages, using WSE in .Net. I want to do the following: 1) Generate a certificate to the system store. 2) Export the certificate from 1) to a file with only the public key, and then import the file as a certificate into the system store, but on another computer. 3) Export the certificate from...
12
22965
by: jimserac | last post by:
I had previously posted this in an Access forum with negative results so will try here. Although this question specifies an Access database, I also wish to accomplish this with a large MS SQL Server database that we have. Question follows: The following SQL statement, used in VBScript,
4
3141
by: Gary Wright | last post by:
I have an Access 2K database split into front and back. Quite often the users want to do some data analysis that I have not created a report for so they want to export some subset of the data into and Excel spreadsheet. Since the data often comes from many different tables, I have decided to create a temporary Access table, put all the data into it then use the Docmd.TransferSpreadsheet command to output the table to a spreadsheet. In...
9
3925
by: jillandgordon | last post by:
I am trying to import an excel file into Access 97. It looks perfectly all right but, every time I try to import it, I get to the lst step and am told that it was not imported due to an error. There is no further explanation. What are the kinds of things that make this happen? Thanks from an obvious rookie. Gordon
1
2690
by: Wandering | last post by:
I know you guys are heavy duty coders, while I do ad-hoc analysis, and rarely write code. And, I may be in the wrong groups because this is about an install issue, and I don't think it's a code issue. But ... Office 2000 was on this machine when it was given to me. I don't own it. After installing Office 97, mine, in a different directory, and checking most of it out, I uninstalled Office 2000. Well, it's a lot easier to upgrade than...
5
31933
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
4
4917
by: Max2006 | last post by:
Hi, We are developing a SQL server based asp.net application. As part of requirement we should allow users import/export some relational data through web user interface. We are investigation which file format would be the most efficient format to import export relational data. So far we came up with two options: XML and Access MDB files and we prefer MDB files.
4
6244
by: David | last post by:
Hi I'm using this code to import data from an excel spreadsheet: DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblStudents", _ "\\Egusersrv\Staff - Shared Work\PE Teach & Report\Imported Spreadsheets/ImportStudents.xls", True Which works fine if the spreadsheet is always in the pre-defined location. Ideally what I would prefer to do is have the user locate
3
7172
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this file but save it as an excel file. The data in this excel file will be imported into an Access database. The
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10430
tracyyun
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...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7141
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5809
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...
0
6010
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4632
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
3
3245
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.