Access 2007 Replication alternatives

Hello, I am really confused, let me run down quickly what I think I have
figured out ...

-Replication has been removed in Access 2007
-Access 2007 is focusing, for advanced use, on being the front end of the
database, and for end user queries / reports, if you want replication you use
a different backend than the Access tables
-Microsoft web site says ahre point for those function, but when I look at
the prices it looks like we are talking thousands ... I have a one employee
business ...
-The microsoft web site does not address (as far as I can tell ... ) the
lower budget alternatives to replication.
- I gather from searching, that some other back end solutions offer
replication,

My replication needs are light weight - I just want to be able to edit some
records at home with out having to worry about wether I remembered to copy
over the database to work before we make changes to the database at work. So
.... record level syncronization. The need is not intense enough to write
custom code or rewrite the front end ...

So my questions ...

-Are the above assumptions correct?
- are there any choices for a different back end than Access that allows for
replication that Access can link to in the free open source to hundreds
rather than thousands price range, and that allows for the same kind
abilities such as auto cascade of updates and deletes and forces referential
integrity?

Thanks!!!
Roger

I have not investigated the connectivity provided by Access 2007, but all
previous versions of Access will allow you to link tables in any
ODBC-compliant server DB as a "back-end," provided you have the ODBC drivers
for that server DB. It is possible that you could use an inexpensive, even a
hosted open-source DB, as the back end, and with good design and
implementation, that would probably work nicely over the Internet.

A remote access software package would be another alternative -- you could
dial in to your office machine, and do the updates in place.

And, in the vast majority of situations, we have been assured that Access
2007 will not "break" applications created in earlier versions -- but,
again, I have not paid much attention to the area of replication, as I have
never had occasion to use it.

Larry Linson
Microsoft Access MVP

"darkroomdevil" wrote in message
...
Hello, I am really confused, let me run down quickly what I think I have
figured out ...

-Replication has been removed in Access 2007
-Access 2007 is focusing, for advanced use, on being the front end of the
database, and for end user queries / reports, if you want replication you
use
a different backend than the Access tables
-Microsoft web site says ahre point for those function, but when I look at
the prices it looks like we are talking thousands ... I have a one
employee
business ...
-The microsoft web site does not address (as far as I can tell ... ) the
lower budget alternatives to replication.
- I gather from searching, that some other back end solutions offer
replication,

My replication needs are light weight - I just want to be able to edit
some
records at home with out having to worry about wether I remembered to copy
over the database to work before we make changes to the database at work.
So
... record level syncronization. The need is not intense enough to write
custom code or rewrite the front end ...

So my questions ...

-Are the above assumptions correct?
- are there any choices for a different back end than Access that allows
for
replication that Access can link to in the free open source to hundreds
rather than thousands price range, and that allows for the same kind
abilities such as auto cascade of updates and deletes and forces
referential
integrity?

Thanks!!!
Roger

Larry, thanks for the info .. you bring to light some new ideas to research ...

I realize that Access can be a front end to most ODBC back ends - MySQL,
etc., but I have never worked with any back ends other than Access as a back
end - if I upgrade to Access 2007 and want to do replication it seems I will
have to go to a different back end ...

- So my first question is, is that correct? - or does one of the things you
suggested work around this?

- Any reccomendations out there for back ends that would be reasonably easy
to use in the same way that Access is used as a back end, with tables linked
and fields bound on forms and cascading updates / deletes ... ? I realize
the next step is just tying it ... but a nudge in the right direction can
save a lot of headache ...

thanks again,
Roger

Not many offering cascading updates/deletes I'm afraid - but, then I never
use them g

Pieter

"darkroomdevil" wrote in message
...
Larry, thanks for the info .. you bring to light some new ideas to
research ...

I realize that Access can be a front end to most ODBC back ends - MySQL,
etc., but I have never worked with any back ends other than Access as a
back
end - if I upgrade to Access 2007 and want to do replication it seems I
will
have to go to a different back end ...

- So my first question is, is that correct? - or does one of the things
you
suggested work around this?

- Any reccomendations out there for back ends that would be reasonably
easy
to use in the same way that Access is used as a back end, with tables
linked
and fields bound on forms and cascading updates / deletes ... ? I realize
the next step is just tying it ... but a nudge in the right direction can
save a lot of headache ...

thanks again,
Roger





--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

"darkroomdevil" wrote

. . . if I upgrade to Access 2007 and want to
do replication it seems I will have to go to a
different back end ...

- So my first question is, is that correct? -

I don't know the answer to this -- but was unaware that replication was no
longer to be supported. As I said, I am not running the Access 2007 beta
because I don't have appropriate hardware to set up a test environment and
NO WAY would I load Beta code on my development/production machine.

or does one of the things you
suggested work around this?

Unless they have removed the feature from SQL Server 2005, SQL Server has
its own replication facility. But, I do not know (and rather doubt) that
replication is in the free SQL Server 2005 Express product.

- Any reccomendations out there for back
ends that would be reasonably easy to use
in the same way that Access is used as a
back end, with tables linked and fields bound
on forms and cascading updates / deletes ... ?
I realize the next step is just tying it ... but a
nudge in the right direction can save a
lot of headache ...

I have not used any of the free or open source ODBC compatible DBs as a
server DB with an Access client. I have, successfully over a LAN, used
Microsoft SQL Server with MDB and over a WAN, with ADP clients, and have
used Informix as a server DB with MDB and DAO over a WAN. Cascading updates
and deletes were handled in Informix by "triggers", which were created by
the data modeling tool used by our DBA. If I recall correctly, the
Enterprise Manager of MS SQL Server allowed you to define cascading
update/delete. The free MSDE does NOT include Enterprise Manager. As the
free MSDE that comes with Access 2003 and earlier is just a somewhat
stripped-down MS SQL Server with a "throttling mechanism," it should work in
similar circumstances for modest user audiences.

There is a SQL Server 2005 Express that is available now, which can be used
with Access 2007 when it comes out (and earlier versions, too, I understand)
provided you have the current .NET framework installed, which doesn't have
the "throttling mechanism" of MSDE. I haven't had occasion to use it, so I
can't comment in detail.

PostgreSQL seems to get the highest praise among current OpenSource
databases, and there are ODBC drivers available for it. Again, I haven't
used it, so can't comment further. MySQL is widely used, but may require a
third-party add-on for cascading updates / deletes.

There was a question in another newsgroup today about differences between
SQL Server Express and other editions, and when I went searching for an
answer I found this ...

http://www.microsoft.com/sql/prodinf...-features.mspx

Apparently, Express does support replication but as a 'subscriber' only. I
don't know enough about SQL Server replication to comment on what that
means, but there's a white paper on SQL Server merge replication at ...

http://www.microsoft.com/sql/techinf...mobileapp.mspx

In Access 2007 Beta 2, I was able to create a replica of an MDB, but the
commands related to replication were not shown when I opened an ACCDB file
(the new Access 2007 file format). So, as far as I can tell, replication
appears to be still supported if you do not convert to the new format.

--
Brendan Reynolds
Access MVP

"Larry Linson" wrote in message
...
"darkroomdevil" wrote

. . . if I upgrade to Access 2007 and want to
do replication it seems I will have to go to a
different back end ...

- So my first question is, is that correct? -

I don't know the answer to this -- but was unaware that replication was no
longer to be supported. As I said, I am not running the Access 2007 beta
because I don't have appropriate hardware to set up a test environment and
NO WAY would I load Beta code on my development/production machine.

or does one of the things you
suggested work around this?

Unless they have removed the feature from SQL Server 2005, SQL Server has
its own replication facility. But, I do not know (and rather doubt) that
replication is in the free SQL Server 2005 Express product.

- Any reccomendations out there for back
ends that would be reasonably easy to use
in the same way that Access is used as a
back end, with tables linked and fields bound
on forms and cascading updates / deletes ... ?
I realize the next step is just tying it ... but a
nudge in the right direction can save a
lot of headache ...

I have not used any of the free or open source ODBC compatible DBs as a
server DB with an Access client. I have, successfully over a LAN, used
Microsoft SQL Server with MDB and over a WAN, with ADP clients, and have
used Informix as a server DB with MDB and DAO over a WAN. Cascading
updates and deletes were handled in Informix by "triggers", which were
created by the data modeling tool used by our DBA. If I recall correctly,
the Enterprise Manager of MS SQL Server allowed you to define cascading
update/delete. The free MSDE does NOT include Enterprise Manager. As the
free MSDE that comes with Access 2003 and earlier is just a somewhat
stripped-down MS SQL Server with a "throttling mechanism," it should work
in similar circumstances for modest user audiences.

There is a SQL Server 2005 Express that is available now, which can be
used with Access 2007 when it comes out (and earlier versions, too, I
understand) provided you have the current .NET framework installed, which
doesn't have the "throttling mechanism" of MSDE. I haven't had occasion to
use it, so I can't comment in detail.

PostgreSQL seems to get the highest praise among current OpenSource
databases, and there are ODBC drivers available for it. Again, I haven't
used it, so can't comment further. MySQL is widely used, but may require a
third-party add-on for cascading updates / deletes.