Importing data from across the web? XML?

R

Roy

Could someone point me in the right direction here?
The current method of importing new data into our db
goes something like this:
1.) txt files received via email
2.) employees clean data and save it in Access
3.) they import it into sql server using a dts package

It's a legacy process that could be updated...which is
where I come in. In about 2 weeks the employees who
do this work will be moved to an off-site location.
Since our network on-site is very secure, they will
have no method of "telecommuting" or VPN'ing into
the intranet to access sql server or anything.

My thought is this: have them continue doing steps 1
and 2 at the off-site office, but give them some
web front-end using which they could upload the new
data remotely into sql server (before you ask, network
security prevents them from simply logging into sql
server remotely using Enterprise Manager). I'm thinking
XML? But I'm unsure. I've never done anything like
this before and I don't want to waste time rabbit-trailing
down unproductive avenues. Anyone have any links or
insights?

Thanks!
 
K

Ken Cox [Microsoft MVP]

Hi Roy,

I think you're on the right track. How about this scenario:

1. User exports the Access table as an XML file.
2. User logs in to IIS Web site and uploads XML file to IIS. (These first
two steps could be automated within Access by a macro).
3. IIS saves the file to a Drop directory.
4. A service on the server watches for new XML files (FileDirectoryWatcher
class)
5. When a file appears, the service parses the XML document and puts the
data into SQL server.
6. If there are no errors, the XML file is copied into the Done folder.
7. If there are problems, send an email message to the sender and move the
XML file to the Problem folder for manual investigation.

Another solution would be to send the Access data as SOAP messages to be
picked up by a Web service on IIS and pushed directly into the database.

Let us know how you make out?

Ken
Microsoft MVP [ASP.NET]
Toronto
 
R

Roy

Hey Ken,
1. User exports the Access table as an XML file.

Can do.
2. User logs in to IIS Web site and uploads XML file to IIS. (These first
two steps could be automated within Access by a macro).

I looked through all the available "Action" commands in the Access
macro section and couldn't find anything that seemed like it has the
potential to auto-upload a file to a remote IIS... but then I'm no
Access guru. Could you let me know which command I should be focusing
on w/in Access?
3. IIS saves the file to a Drop directory.
4. A service on the server watches for new XML files (FileDirectory
Watcher class)
5. When a file appears, the service parses the XML document and >
puts the data into SQL server.

When you say a "service" above, do you mean a small .NET app that is
kicked off by windows scheduler every 5 mins or so? I don't think
that's what you're saying, but I'm unfamiliar with the terminology...
If I were looking on msdn for more info on utilizing "services" what
section would I be looking for?
6. If there are no errors, the XML file is copied into the Done folder.
7. If there are problems, send an email message to the sender and
move the XML file to the Problem folder for manual investigation.
Another solution would be to send the Access data as SOAP
messages to be picked up by a Web service on IIS and pushed
directly into the database.
Let us know how you make out?

Most definitely, thanks for the tips!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top