Append records in linked table to MS SQL table via OLEDB?

[BigTeeJay]

Greetings,
Does any know of a way to take a linked table, and
append all the records into a table is MS SQL Server?

I know I could attach to the table via ODBC and then
just dump the contents from the linked to the attached,
but I want to use OLEDB (since I have a LOT of data
to move, and OLEDB is faster).

I also know that MS SQL Server has the data import
wizard, but this wont work as I have hundreds of tables
to append to a master MS SQL table.

Essentially I am taking a Paradox table, linking it,
and trying to find a way to do something like...

sSQL = "INSERT INTO MS_SQL_TBL " & _
      "SELECT * FROM LINKED_PARADOX_TBL"
rs.open sSQL, conObjPointingToSqlServer

...but the LINKED_PARADOX_TBL is only known to
MS Access (not to the SQL Server that would be
recieving the sql command.)

It would be really cool if I could just do something
like...

rsSQL.append(rsParadox)

I also would rather not do something like...

do while rsParadox.EOF = False
 with rsSQL
   .addnew
   !col1 = rsParadox!col1
   !col2 = rsParadox!col2
   etc...
   .update
 end with
loop

...for every table (I know its probably possible, but
that would take entirely too long to do for every
column of every record of every table).

Tj

 

[cmmrfrds]

The fastest way would be to make the Paradox database a linked server in SQL Server. If the Paradox tables are set up as linked servers then you can use standard sql syntax inside of SQL Server. If you have Access 2000 or above then you can make an Access Project and write stored procedures to make your update queries. You may need to use the OLE Provider for ODBC for the Paradox tables If there is no native OLE provider for Paradox. It is not difficult to make a linked server, so any competent DBA should be able to set it up for you.

 

[BigTeeJay]

I'm no DBA (and the DBA for our project knows even less than I do... dont ask, its a long, sad story

Would you have any good reads on how to do this? I've been searching MS's site, etc... but I havent been able to find any info that is good enough to help me build my own link. At the very least an example of what its asking for for each of the settings (so I can find out the equiv for my needs).

Regards,
Tj

 

[cmmrfrds]

Here is a link to a Microsoft explanation. You would need to use the ODBC option for paradox.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_4uuq.asp

 

[cmmrfrds]

Here is another source.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8992