copy a table from SQL SERVER to Access DataBase

[Sheel Gohe]

Hello Friends, I am facing a problem in copying a table from SQL SERVER to Access DataBase. I have identical tables in access database to and what i am trying is creating 2 datasets, one is connected to SQL Server DB and another one is connected to Access DB. Now when I use the "Copy" command of dataset to copy data from one dataset to another dataset and then updating the adapter it throws no error but access database doesnt reflects refreshed data. Do you guys have some working code on this problem. thanks in advance Regards Sheel Sheel Gohe

[Dave Kreskowiak]

Well, I've never had to do this. And, frankly, don't want to. Off the top of my head, you can't do this between two different databases because the underlying database types are different. For instance, SQL Server supports the bit type, where Access does not. The Data/Time storage formats are different, different Integer types are supported, Currency, ..., yada, yada, yada. The Copy method will NOT translate this for you. So, no, I'd say you can't do this using this method. You'd have to read each individual record and field from the source table, translate the data, then put the new data in an Update or Insert query in the Access database. The other alternative is to construct a DTS job to do the export for you. This will, of course, depend on the Access file being put someplace where the SQL Server can write to it and you can pick it up after the job completes. Dave Kreskowiak Microsoft MVP - Visual Basic