MS Access and MS SQL Database

[CypherDrive]

Hi,

Let's say I have an MS Access Database and An MS SQL Database.
They have the Same Structure and same records. I'm planning to Sychronize
their content. If
I query on an ms access Database and use a filled dataset. Im planning to
UPDATE both the MS SQL and MS Access simultaneously using the Updated
Dataset. is this possible?
Are there available tools to do this in VB.NET? Do you have sample codes?

Thanks again..

 

[Cowboy \(Gregory A. Beamer\)]

Can you do it? Yes.
Should you do it? Good question.

As long as the structure is the same, you can fairly easily do this. One way
to play this game is get a DataSet from SQL Server with the same data and
add the changes from your dataset. The same is true back to Access. This is
fairly safe too.

If you know both databases are ALWAYS in sync, you can simply create a
DataAdapter for each with the same command and apply changes. There are some
caveats with mixing metaphors (Access and SQL Server), but if you are using
fairly standard ANSI SQL for your select query on your adapter, things
should match up rather well. If this does not work, you can go back to
creating a DataSet from each and adding changes to one DataSet and using it
to go back to each (first solution).

Why do you have two databases? The reason I ask is if this is to sync up
offline data, you are better to sync it when a user requests a sync. In
these instances, there are better ways to sync information. I would also
look into putting SQL Express on the remote machines, if this is the case
that is, as you can sync SQL to SQL much easier. In addition, the SQL
Express database is a better solution for mobile apps. I realize you may
have some Access forms, etc, that stop this from happening.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

********************************************
| Think outside the box! |
********************************************

 

[Miro]

Why do you require the Access database as well?

Perhaps the solution is NOT to constantly write the same record(s) twice.
(once on sql and the other on access)
-lets say the sql one commits but the access one does not - what then?

Perhaps if you are just trying to give a portable database and for whatever
reason you do not want it to be sql, why not
at one point export out everything you require to the Access database at
once.

Can you give us some reason/example as to why you are trying to keep the two
in sync ?

Miro

 

[Cor Ligthert[MVP]]

Cypher Drive,

Nobody forbits you to use OleDB with SQL Server,

As you do that, then you can use all the same commands, while you would use
the Connection and by instance the OleDBDataAdapter twice, one time for SQL
and one time for Jet and even do everything in the same procedures.

Just my thought

Cor

 

[Jialiang Ge [MSFT]]

Hello CypherDrive,

To UPDATE both the MS SQL and MS Access simultaneously using the Updated
Dataset, we can consider "creating a DataAdapter for each with the same
command and apply changes" as Gregory A. Beamer suggested. To resolve the
problem "lets say the sql one commits but the access one does not - what
then?", you may consider using Transaction:

For example:

Dim sqlTrans as SqlTransaction = sqlConn.BeginTransaction()
Dim oledbTrans As OleDbTransaction = oledbConn.BeginTransaction()
......
'Assign each DataAdapter to the proper transaction.
sqlAdapter.UpdateCommand.Transaction = sqlTrans
oledbAdapter.UpdateCommand.Transaction = oledbTrans
......
'do UPDATE for both data sources.
'If both succeed, we call:
sqlTrans.Commit()
oledbTrans.Commit()

'if any one fails, we call:
sqlTrans.Rollback()
oledbTrans.Rollback()

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jialiang Ge [MSFT]]

Hello CypherDrive,

I am writing to check the status of the issue on your side. Would you mind
letting me know the result of the suggestions? If you need further
assistance, feel free to let me know. I will be more than happy to be of
assistance.

Have a great day!

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[CypherDrive]

Ge,


Thanks..

Regards,

Cypdrv