Replicating a sql-server table from a .net application

  • Thread starter Thread starter Remco Hulshoff
  • Start date Start date
R

Remco Hulshoff

I'm not quite sure this is the correct group te post this so please correct
me if 'm wrong.

My goal is to synchronize the contents of a offline access database with a
online sql-server. In order to do this in a robust manner i'm trying to
implement to following steps:
1. Clone Table to Table2 in the sql-server
2. fill Table2 with the data from the access database
3. rename Table to Table1 (sql-server)
4. rename Table2 to Table (sql-server)
5. remove Table1 (sql-server)

The problems start with the cloning. I am currently trying to do this using
ADOX via interop but i am running into various problems here. I need a full
clone of the original table with all its columns, indexes, keys and
properties.

My questions to this group:
1. Is ADOX the way to do this?
2. the Columns.Append method expects either: (ADOX.Column) or (string,
DataTypeEnum, long). I'm trying to use the first one but i can't seem to get
the optional parameters working (in c# they are not optional and i can't
find the correct values). I'm having the same problems with Indexes.Append
etc

Any help would be highly apreciated,

Remco Hulshoff
 
Hi Remco,

My questions to this group:
1. Is ADOX the way to do this?
2. the Columns.Append method expects either: (ADOX.Column) or (string,
DataTypeEnum, long). I'm trying to use the first one but i can't seem to get
the optional parameters working (in c# they are not optional and i can't
find the correct values). I'm having the same problems with Indexes.Append
etc

Any help would be highly apreciated,
Click to expand...

Why don't you use native SQL stataments to modify schema?
IMO it should be easier.
 
Hello Miha,
Why don't you use native SQL stataments to modify schema?
IMO it should be easier.
Click to expand...

I would love a simple solution. however i am also rather new to sql-server.
could you give me a view pointers on how to do this?

thx,

Remco Hulshoff
 
Hi,
The possible solution from my side is to get the content
from the both the Access and Sql server to two different
xml files. say xm1-access and xml-sql. now delete the data
from the sql table using some delete statement and read
the xml file and write it into the Sqlserver. While
writing if you have any problem just restore the data from
the existing xml-sql file. if Every thing works fine, just
remove the xml-sql.

Hope this requires just only two database operations.

thanks
srinivas moorthy
 
Hello Srinivas,

Although i like your solution, for my scenario it is not an option. the
system wich will be running the synchronisation process will almost
certainly be a bandwidth impaired system (ie modem). Retrieving all the data
from the sql-server to the client and then possibly transferring it all back
is thus not an appealing idea. This is why i opted for the temptable
solution in the first place.

Thanks for your input,

Remco Hulshoff
 
Hello Remco

Have you looked into Data Transformation Services (DTS) This is a SQL Server
service specifically designed to do what I understand you atre attempting to
do. If I read you right, you are not really synchronizing the two
databases, but only cloning an Access table to a SQL-Server database, right?

DTS is the way to go..imho

Ibrahim
 
then I think you can use the DTS from the SQl Server to
connect to the Access on a Remote Machine.

thanks
srinivas moorthy
 
Back
Top