Replicating a sql-server table from a .net application

Remco Hulshoff

unread,

Dec 12, 2003, 6:11:25 AM12/12/03

to

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

Miha Markic

unread,

Dec 12, 2003, 6:20:33 AM12/12/03

to

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,

Why don't you use native SQL stataments to modify schema?
IMO it should be easier.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Remco Hulshoff

unread,

Dec 12, 2003, 7:14:48 AM12/12/03

to

Hello Miha,

>> 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,
>
> Why don't you use native SQL stataments to modify schema?
> IMO it should be easier.

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

srinivas moorthy

unread,

Dec 12, 2003, 9:11:14 AM12/12/03

to

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

>.
>

Remco Hulshoff

unread,

Dec 12, 2003, 9:18:38 AM12/12/03

to

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

IbrahimMalluf

unread,

Dec 12, 2003, 11:42:29 AM12/12/03

to

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

"Remco Hulshoff" <r.hul...@heritas.nl> wrote in message
news:eM$BwCKwD...@TK2MSFTNGP09.phx.gbl...

srinivas moorthy

unread,

Dec 13, 2003, 1:23:07 AM12/13/03

to

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

>.
>

Remco Hulshoff

unread,

Dec 14, 2003, 7:35:53 AM12/14/03

to

I will look into this.

Thank you,

Remco Hulshoff

unread,

Dec 14, 2003, 7:36:00 AM12/14/03

to

I will look into this.

Thank you,

Remco Hulshoff