Database Discussion - using macro to export data from access

Print Page | Close Window

using macro to export data from access

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=5935
Printed Date: 30 March 2026 at 7:27am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: using macro to export data from access

Posted By: simflex
Subject: using macro to export data from access
Date Posted: 24 September 2003 at 6:23am

Hello friends and helpers,
I have a tasking task.
I need to automate the task of migrating data from access 2002 database to sql server db.
I can do this and have done this several times before using sql server's DTS.
However, what is different about this is that the access db that needs to be migrated sits on a user's desk top.
All information I gathered so far points to using access macro to automate the task of exporting this user's data to a text file in a central location and then finally migrating this data to sql server.
Can someone please help me with the steps of using an access macro to automate the exporting of data to a text file.
Once this part is successfully completed, I can take it from there as far as using dts to migrate it to sql server.
The access help menu for using macro to automate this task has not been that helpful.

If there is any links or articles on how to accomplish this, please let me know
Thanks in advance

Replies:

Posted By: Flamewave
Date Posted: 24 September 2003 at 9:00am

You can import the data from the access database to the sql database, but this wont copy over any of the keys or indexes, so you will have to set those back up manualy on the sql server.

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: simflex
Date Posted: 24 September 2003 at 1:44pm

I agree with your comments but I normally use the upsizing wizard which preserves everything from access rules, contraints to pk and auto numbering.

This case is slightly different in the sense that the client's access db is sitting on his desk top.

So trying to export data using DTS to his access db won't work.

What we are trying to do is use access macro to export data to a text file outside of his desktop and then export data from there to our sql server that is sitting on a remote site.

My problem is understanding the steps in using a macro.

So far, having followed the example from the access help menu is causing an error that says I "cannot update the database because it is a readonly db"

I don't understand this error because:

1, I am not updating the access db and

2, I can write to it.

Any additional help would be greatly appreciated.

Posted By: Flamewave
Date Posted: 25 September 2003 at 3:51pm

Ahh, I was under the impression that this was just a one-time deal, not something that you wanted to automate. I'm not very familiar with macros myself, so I'm afraid I can't help you. I'm sure you can do a google search and find something though.

-------------
- Flamewave

They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.

Posted By: eksimba
Date Posted: 25 September 2003 at 3:57pm

Would it be easier to just copy the entire .mdb file to the location off of the user's desktop? That way you have access to the entire database, and not just the text-file extract of it...

-------------
- eric