September 28, 2003 at 7:48 am
Dear all,
Please let me give you a brief history of what is going on and then ask how you can help me accomplish this project more efficiently.
A dept within our company contracted a cendor to design an access db for them.
There are a total of 7 access dbs involved and each db has atleast 15 tables.
Recently, management decided for us to develop web map apps (GIS) to monitor a couple of ongoing sewer spills project.
To implement this project, we have been asked to move all tables from these access dbs to a sql server db and do it in such a way that the dept currently using this access application will continue to use it while we will automate a process to append any new data from them to the tables in sql server db.
Problem is these access databases are sitting on the user's desktop while the sql server db sits on our remote server.
The first step was to move these access dbs from the user's desktop to a central repository and eventually move them to sql server.
I have succeeded in using access macro to not only move these tables to a central repository to an excel spreadsheet but also automate the process to append any new data to the tables in a central repository.
Right now, I have moved these tables to sql server db using dts.
I have also scheduled a couple of them to append additional data to the tables in sql server db.
My question is that while the process I used works, is there any script that can do this for me more efficiently because using the method I have currently employed, requires scheduling dts to move the tables one at a time giving the fact that the tables are in excel spreadsheet.
Thanks in advance and I hope my explanation is clear.
If not, please let me know.
September 28, 2003 at 3:01 pm
Cannot comment on the 'efficiently', suppose DTS will be faster.
Alternative is to select the data directly from Access databases.
Select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\Servername\Shared\Example1.mdb";
User ID=Admin;Password=;')...Tablename
Can also have a look at OPENROWSET.
September 29, 2003 at 5:59 am
thanks for your response.
Select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="\\Servername\Shared\Example1.mdb";User ID=Admin;Password=;')...Tablename
The method above would be great for what I am trying to do but the problem I have is I can't see how this will be possible when the sql server db is sitting on a remote server while the access database we are trying to move is sitting on someone's pc.
I am obviously missing something here.
September 29, 2003 at 6:57 am
No sure if I understand fully but why not change access db's tables to linked tables and update sql direct. Do not understand where Excel comes into this though.
Far away is close at hand in the images of elsewhere.
Anon.
September 29, 2003 at 7:40 am
hi David!
Let me explain how excel came into play.
There are 3 ways that I know of how to automate the process of moving data from access to someplace (either another access, sql server, excel or textfile).
One is to use transferText macro action, the other is to use transferSpreadsheet macro action and the other is to use TransferDatabase action.
Of course you can always write a vba module to perform this task.
Now I have written a vba code that works but I don't know how to automate this vba code to run move data from access on a daily basis.
So I abandoned using vba.
I tried transferText but it looks all mushed up after the transfer.
The idea method would have been to use transferDatabase because then instead of having to perform 75 dts scheduling ( one table at a time, I could schedule one db at a time) but again I was getting errors that no one could figure out.
So as last option, I decided to use TransferSpreadsheet because it transfer the tables into an excel spreadsheet which will then allow me to use dts to move them to sql server db.
Again using the link option isn't working for me because the access database can't point to the sql server db sitting on a remote server.
Hope I explained it a little bit better.
September 29, 2003 at 8:09 am
Let me see if I have got it this time. You are currently exporting each mdb from each client to excel on a common share that sql can see as well. You then import the excel data into sql.
Access and Excel have an AUTO_OPEN option that will execute a sub names AUTO_OPEN when the file is opened. By using this and scheduling the file you can activate a daily process. However this means that the client needs to leave their pc on and you can run a scheduler on the pc, a bit messy.
Can you create a common mdb on the share and get all 7 mdb's to update it and then get sql to access the single mdb to transfer data.
Far away is close at hand in the images of elsewhere.
Anon.
September 29, 2003 at 9:38 am
yep, you are right on the money with your understanding of what I am trying to do.
I have finally gotten it to work the way I want it.
I ran the idea to our network/system admin guy.
He manipulated the user's desktop which exposed it to the remote server where the sql server db is sitting.
Doing this allowed me to directly schedule a dts data import from access to sql server.
It is so seamless but I should have asked him sooner.
Thanks as always for your contribution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply