Export to Excel and then Import back to Access (1 Viewer)

Mikeyboi92

Registered User.
Local time
Today, 12:44
Joined
Aug 1, 2013
Messages
10
Hi All,

I hope i have put this into the correct place. I gathered queries as i use a query to export my data.

Right, I have a slight issue that i cant seem to resolve.

I have created a database, it has a table that a query runs on. when the query is ran it looks for any records that have a 'true' value on a field called 'Management Escalation' it then exports all of these records to Excel and prompts you where to save it.

This is all working fine and it is exporting as it should be, the problem i have is Importing it Back into Access.

When i try to import it back into the table it was exported from i keep getting a message advising 43 records were deleted.

I have tried it just exporting then re-importing with no changes and i have the same issue. I tried changing one of the entries and re-import it to Access to see the change and again it failed and advised 43 records deleted and the original table was unchanged.

I have combo boxes / yes/no field / dates which i thought might be causing the issues but i have tried everything and its not working.

I can however import the excel back into Access using the create new table option and all the details are there and correct including the combo boxes etc.

Im soo confused, please can someone help me :(

:banghead:


Thank you !

Again sorry if this is in the wrong area..
 

JHB

Have been here a while
Local time
Today, 13:44
Joined
Jun 17, 2012
Messages
7,732
Why this export and afterwards import, why not keep it in MS-Access?
How does the data look like when you export it, and how does it look when you import it?
Post you database with some sample data, (zip it).
 

Mikeyboi92

Registered User.
Local time
Today, 12:44
Joined
Aug 1, 2013
Messages
10
Hi JHB,

Thanks for the reply.

Basically i work on a case management team and have created a database so that myself and my colleagues can manage our cases more effectively.

If a case needs a manager escalation (bit more of a kick) we have to add some extra details etc. Our manager then clicks on a button to export this as Excel so that it can be sent to another team in the business to update etc.

now the Idea is that they can update this (in a way that i will specify to them once this is working) and we can then import it back into Access so that it updates the given records accordingly.


How does the data look like when you export it, and how does it look when you import it?

When i export it, the headers are all the same, they have all the right information in there, The yes / no Values are set to True / false etc it all seems fine.

IF i import this back by creating a new table it works fine. no problems at all. and looks exaclty as it should be.

I kjust cant get it to append to an existing table from which it was originally exported From :(

When importing back to Access are you able to append specific records based on the Primary Key or do you have to have new primary keys?
:banghead:
 

JHB

Have been here a while
Local time
Today, 13:44
Joined
Jun 17, 2012
Messages
7,732
Do you after you have export the data and before you import it again, delete the data from the table, (else you'll get a double set or you'll get a key validation)? If the primary key is an auto field type, then you can't set the value for that field.
But for me it sound more like you need to update the change fields.
I would do it on this way:
Import the data to a temporary table, and the run an update query, (and the same if you want to append the data, then use an append query).
 

Mihail

Registered User.
Local time
Today, 14:44
Joined
Jan 22, 2011
Messages
2,373
Why "other teams" are not allowed to update directly the records in the database ?
 

Mikeyboi92

Registered User.
Local time
Today, 12:44
Joined
Aug 1, 2013
Messages
10
Hi JHB

Thanks for the reply. I will be applying the way you described I.e import to new table then append :) thank you.

Mihail, that in an ideal world would b the way forward however they dont have access ttothe shared drive that the back end is stored on and so would not be able to update the data. I have thought about that and will be doing that as soon as I get the go ahead really.

Thanks guys :)
 

Mihail

Registered User.
Local time
Today, 14:44
Joined
Jan 22, 2011
Messages
2,373
Mihail, that in an ideal world would b the way forward however they dont have access ttothe shared drive that the back end is stored on and so would not be able to update the data. I have thought about that and will be doing that as soon as I get the go ahead really.

Let me understand, please.
WHY " they dont have access to the shared drive that the back end is stored on" ? Because they can't access this drive via network or because the access rights ?
You transport the data (Excel sheet) between computers by using physical devices, such as memory sticks or CDs/DVDs ?

Anyway, I think to an approach that, in my opinion, is better than to use Excel.

Design a new database (NewDB). Should have a single table (NewTable) and a very simple interface that allow a user to manage data in that table.

Use this new database as BE for your main FE (by using the Linked Table Manager, link the NewTable from NewBE to your main FE).
From now all you have to do is to manage (from your main FE) to update this table with properly records then distribute this NewDB to the users (as you already done with the Excel file).

You should be able to do all this in minutes because you already have the "tool" "the query is ran it looks for any records that have a 'true' value on a field called 'Management Escalation".
Use this query to fill the NewTable in the NewDB.
 

Mikeyboi92

Registered User.
Local time
Today, 12:44
Joined
Aug 1, 2013
Messages
10
Hi Mihail.

They do not have access as they work in a different department.

We used to just send an email with the escalation details but this is becoming more and more inefficient.

The BE is on a shared drive my department can access and so myself, my team, team leaders can all use the FE to update it etc.

I will be using yhe import to new table then append method that JHB advised of as this solves my issues.

Eventually I hope to have a new shared drive that both our department can access then I will designing log on for their department with forms etc so that they can update the records directly etc.

There is a a lot if office politics haha
 

Mihail

Registered User.
Local time
Today, 14:44
Joined
Jan 22, 2011
Messages
2,373
What I try to say is that you can email as well the small, new created, database.
 

Mikeyboi92

Registered User.
Local time
Today, 12:44
Joined
Aug 1, 2013
Messages
10
We dont just use this for escalation my database I created is also used to managing our cases work better. We have other systems we use but it they were never easy to 'see' everything if you will and so I created database to collate all the info in one place so was easy to work with the other programs.

The escalation part is just one bit and I dont see why I would need to create another new database when I can create a new tabke for the imported data which only has changes on it theb append this to the existing tables so that my team can see the escalation update
 

Mihail

Registered User.
Local time
Today, 14:44
Joined
Jan 22, 2011
Messages
2,373
I wish to be sure that you understand that I sustain my idea not because is MY.

Access has powerful tools to validate data. By using forms bounded to the table you will avoid wrong data to be entered.
I'm sure that now is an issue for you to validate data that is entered in Excel cells. If is not yet, certainly will be.

The small database is intended to replace the use of Excel.
I think that you don't understand how this small database should do the job. So lets try to explain with my poorly English:

From the main FE you update the NewTable in the NewDatabase.
Email this NewDatabase to the users
When this NewDatabase is returned from users to you via email, save it and REPLACE the existing one.
From your FE, manage to update the main BE.

Exactly how you work now with the Excel files.

JHB has give you good and useful advices about how to deal with Excel files.
OK. This is doable and solved.

But what I say is that is a much better idea to use Access (and only Access) in order to avoid troubles in the future.

Good luck !
 

Users who are viewing this thread

Top Bottom