Access to SQL questions

[travismp]

I am running 2 Access DBs in house. I am getting ready to build a new website and I want to use SQL backend on my server. I cannot convert the access dbs to sql for various reasons.

So is it possible to have a SQL Express Server running on my hosting server and have Access Database in-house, and once an hour the SQL server connect to the Access DB and mass import or override the data from access into the SQL?

If the answer is YES which I am sure it is HOW DO I DO IT? Any online tutorials maybe?

THANKS MY FELLOW IT NINJAS.

 

[SQL_Hell]

How many records are we talking about?


Yes it is possible but not really a great idea... not very scaleable.

Why cant you upsize the access database?

 

[travismp]

How many records are we talking about?


Yes it is possible but not really a great idea... not very scaleable.

Why cant you upsize the access database?

Why is it not a great idea?

 

[MSAccessRookie]

I am running 2 Access DBs in house. I am getting ready to build a new website and I want to use SQL backend on my server. I cannot convert the access dbs to sql for various reasons.

So is it possible to have a SQL Express Server running on my hosting server and have Access Database in-house, and once an hour the SQL server connect to the Access DB and mass import or override the data from access into the SQL?

If the answer is YES which I am sure it is HOW DO I DO IT? Any online tutorials maybe?

THANKS MY FELLOW IT NINJAS.

I imagine that you could do it, but there will be a lot of potential traps top take into consideration. In essence, I would want to have a VERY good reason Not to change the Access Back End. One that outweighs the Possibility of Database Corruption during one of the regular imports, that is

 

[SQL_Hell]

Why is it not a great idea?

Numerous reasons, here a few:

1. mass importing data every hour is going to be quite a performance hit on your databases.

2. What happens in the future, the database will grow and grow and maybe one day there is too much data to process in an hour.

3. Someone will need to constantly monitor this mass import process, do you have resources for this?

4. SQL express does not have any of the nice features like SSIS in the full version of sql server. So mass importing will be a slow sluggish afair.

5. Not upsizing and keeping your data in Access is not a permanent solution because at some point the database will grow bigger than Access's limitations, then you have no choice but to upgrade to an enterprise database engine at which point everything including your mass import will need to be changed.

 

[travismp]

You are correct on all accounts and I know the best practice would be to switch to SQL which I will do within the next few months. I was hoping to get another project finished NOW set up without the SQL backend. I will go another route. Thanks.