Solved

MS Access sync

Posted on 2014-07-24
21
234 Views
Last Modified: 2014-08-13
My company has MS Access(2007) database that connected to ERP system locally in the network. The ERP and MS Access can't not be changed for anything. It will stay as it.

Now I want to a eCommerce website to write/read the MS Access (Basically, allow customer to purchase products on line).  Just like if order is created on the website, the inventory inside of the MS Access database will be reduced, as a example. What is the best solutions? Replicate the MS Access or syn out to the cloud? Or something else...

The bandwidth in the company office is not very fast 1.5 up/down.
0
Comment
Question by:ITsolutionWizard
  • 8
  • 6
  • 5
  • +1
21 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
You have a couple of options:

1. Look at Access 2013 in Office 365.  With A2013, you have "Web apps", which allow you to present forms through a SharePoint site.  Data is stored in Azure.

 On the back side, you can have a Access desktop database (what your used to with Access), connected to the same database.   So you can fetch entered data, and then process it against your ERP or local JET/SQL dbs.

2. Use something like ASP.Net or PHP and do a true web site and connect to your JET/SQL dbs.

Jim.
0
 
LVL 84
Comment Utility
Your website would not be able to read your LOCAL Access database (unless the webserver machine is on the same network as the Access database, which is unlikely), so you'd have to do something along the lines of what JimD suggests, or perhaps this:

Create a routine in the website that will dump the orders to a standard Text file with a specific format, then create a routine in the Access database that will periodically check for new files, download them, and import them. It's not exactly "real time", but it's generally good enough for ordering information.
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
Need real times
0
 
LVL 84
Comment Utility
Then you can't do this with a local Access database. Access databases - or more correctly, ACE or JET databases - do not support remote connections, so unless your web server is on the same physical network as your Access database, it cannot directly insert into that database.

And you could set the frequency of the polling/insert to be every 5 minutes (or every 30 seconds, for that matter). In almost every situation that's often enough to keep track of inventory.

Or as JimD suggested, convert the data store over to SQL Server/Azure, and then modify your Access Front End (FE) to connect to that database instead of the Access backend. This would very likely require some modification to the Access FE, however, so be aware of that.
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
Scott,

Like I already mentioned, no change with the Access and ERP. It stays as it condition so please don't bring this up again.
Or no convert data from access to sql server. It won't happen.
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
So far, it seems like both are agreed it is impossible to make it real time. Then we have to wait other experts' comments.

Thanks
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<So far, it seems like both are agreed it is impossible to make it real time.>>

 Not at all...it depends on your setup.   Is your web server on your network?   If so, then it's entirely doable.

If not, can you create a VPN?

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
I should add that I would not write to the JET tables that way (over a VPN), but certainly a process such as what Scott outlined would be more than adequate and appear real-time.

 Office 365 is a cheap option as well, even if your stuck with A2007 in-house.

Jim.
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
Jim: Scott's solution is not a real time so It won't work to my case. I just wonder you mentioned about Office 365 with Access 2013. And today you mentioned about Office A2007 with Office 365.

Do you have any ideas to share with me how Office 365 working with Access 2007?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Do you have any ideas to share with me how Office 365 working with Access 2007? >>

 With Office 365, you could build a web site that would read/write to the Azure database.

 From your Office with A2007, you can read/write to the same database.

 The idea is fundamentally no different then what Scott said, but rather than using files, your using tables in a DB.

 To offer anything more, you'll need to provide more specific details on what your current setup is, and what your looking to do.  For example, both Scott and I are assuming Access 2007 is talking to an JET/ACE DB, but if your using SQL Server for the backend, then it's a different deal entirely.

 And it's not clear to me what's in Access 2007 vs what's in the ERP system, so for your web site, why does A2007 get involved at all?

Jim.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The constraint of "no changes allowed" is pretty restrictive when you are trying to add new functionality.  Are you supposed to wave a magic wand?

Although you can have a website connected to Jet/ACE, no one who knows anything about either database engine would recommend it and I certainly wouldn't want that BE being shared by my client/server users.

Unless the website and the Access application can share the same physical database there is no real time option.  And even if they can, putting your BE on the web server and sharing it with your entire organization is not going to go over well with the security folks given that it will be on the wrong side of your fire wall.

Your best option is going to be something "near real time".  For this, you would copy the Jet/ACE tables to SQL Server.  The SQL server database would be linked to the website.  You'd need to create a log table to log all changes.  Then you would need a process that was scheduled to run periodically throughout the day to pick up all unprocessed updates, apply them to the Jet/ACE BE and then mark them as applied.

Effectively, you are writing your own replication application which will ultimately end up being considerably more complicated than my explanation above.

To be ultra safe, I would also write sync code to compare both databases to ensure that nothing got by the replication code.

If the web database and the client database were both SQL Server, you could use replication to keep them sync'd and that's about as close to real time as you'd get but you can't go there because you can't change the Access app at all.
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
Jim the E R P system is talking to a2007. All I want a website connected to a2007. The website is hosted out side of the network.
The reason I host outside is because the office bandwidth is only 1.5 up and down.
ThAnks
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Well you have a couple of options:

1. Keep JET/ACE and host the web site yourself so that the web site and db are on the same network.

2. Switch to SQL Server (Express edition is free).  Your web site and/or Office could be somewhere else then.

3. Go with files as Scott suggested and really, you should consider this as it may not be the situation you think.   It can be structured a couple different ways, but certainly you could have an Access app scanning an "in box" very fast and then push a file back to the web site in response.

  It's hard to say which would be the best approach not knowing what you have planned for the web site, but #3 is not as un-workable as your thinking.

Jim.
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
The ERP is written by Delphi 7. And we don't have anyone who know how to deal with it. So Scott's suggestion may not work in my case.

Option 2 won't work because I already mentioned that upgrade to SQL Server is not my option.
Option 1 is what I try to avoid because of the maintenance.

So to me....still no solution....

However, thank you for all your inputs and time. I really appreciate your helps.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
There is no real time solution but there is still the self-coded replication option that I suggested.  Don't hold your breath waiting for a real time solution.  Your constraints make it impossible.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<Option 2 won't work because I already mentioned that upgrade to SQL Server is not my option.>>

  I said "switch", but it doesn't have to be that.  You could use SQL as an intermediate.  Web site talks to SQL, A2007 talks to same DB, and then uses it's local DB to work with the ERP system.

  But if it were me,  I'd change to SQL locally as well and simplify everything plus get all the benefits that SQL offers.

<<Option 1 is what I try to avoid because of the maintenance. >>

 Not sure I understand that.   You asked for something that would work, but your not willing to do anything to get there.

 As Pat said, your constraints make it impossible.  JET/ACE was never designed to work over a WAN and can't unless it is a very high end point to point (>10mb/sec with latency <10 milliseconds).   The cost of a point to point like that on-going would far exceed the cost of doing anything else in a very short time (on the order of thousands of dollars each month).

Jim.
0
 
LVL 84
Comment Utility
All I want a website connected to a2007. The website is hosted out side of the network.
As JimD said, unless you are willing to invest in a very high-bandwidth VPN between your network and the webserver, then you can't do this.Access databases do not support remote connections, so they cannot be accessed outside of the network where they're hosted.

So unless you change the restrictions and conditions in place, then you cannot do what you what.

If you can change those conditions/restrictions and move to something like the replication idea Pat mentioned, or one of the intermediary solution JimD or myself mentioned, then you should be okay. Those would not be 100% realtime, but they can be very, very close to it.

Otherwise I'm afraid it's "Sorry, but you can't do that".

FWIW, I'm under contract with a big ERP company, and we often hear these same questions - I want my website to show "realtime" data for my inventory so customers can order. All too often we find the web hosting companies have restricted remote access to the databases, and we end up with a file transfer type of solution, as I suggested. Even when the customer has very high e-commerce traffic the file transfer/intermediate database solutions we provide work very well, and the customers are surprised at how up-to-date their website inventory ends up being.
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
No file transfer is not the solution.
The web hosting we use is dedicated server and we have full control. It is just out side of the network that  e r p resides.

We will wait for more experts inputs. Thanks
0
 
LVL 84
Comment Utility
There's nothing else any Expert can tell you. If the Access database is not on the same network as the web server, then you can't do this by directly connecting to the Access database. This is a limitation of Access, and there is no "workaround". There was at one point with RDS, but that technology has long since been deprecated (and it was buggy and prone to issues even then).

So your answer is simply this: Given your conditions and restrictions, you can't do this.

If you can come up with new conditions and/or restrictions - like you could perhaps use Jim's suggestion of an intermediary database that links into the Access database, and then also links into the web server somehow - then you could do this.

But if you keep the same conditions and restrictions then ... we're back to "You can't do this".
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
It is ok. Give a little time and chances for other experts to express their suggestions. It does not hurt. Maybe we all will learn something new.

Thanks
0
 
LVL 84
Comment Utility
You can allow as much time as you want, but you're not going to find a realtime solution with your conditions. It's not my project, so don't really have any investment in it, but you'll do yourself a favor if you go back to the people who have setup the restrictions and condition and try to come to some sort of agreement and a new set of restrictions and conditions.

But it's your project ...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
Complex SQL 10 32
subform does update on ms/access 2 14
Best RAID for a BDD Oracle 4 13
Read about achieving the basic levels of HRIS security in the workplace.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now