C# Programming
08-27-2009, 12:06 AM
I am writing an app that requires centralizing a number of Access App's data files into one SQL server. Albeit separate databases within it.
I know the Access Upsizing wizard can migrate access tables to SQL. As can the SQL server import wizard. However, I need to be able to schedule this and request it at any time from the server.
Getting the Access mdb files onto the server isn't a problem, that's all handled with WebServices. However, I can't for the life of me find a way to automate the table creation and data insertion for the sql server.
I have come across a number of resources listing how to pull data from a datatable object and create the database on the server using SMO (SQL Managemant Objects, i think) But I can't see any resources on how to actually pull the Access data into a Datatable object in the first place. Either way, this method obviously will incur some speed penalties, also maybe some problems with datatypes etc... So finding a way to automate the sql import is by far the better idea!
If anyone has any ideas I would be so grateful!
Thanks a lot.
Ben
I know the Access Upsizing wizard can migrate access tables to SQL. As can the SQL server import wizard. However, I need to be able to schedule this and request it at any time from the server.
Getting the Access mdb files onto the server isn't a problem, that's all handled with WebServices. However, I can't for the life of me find a way to automate the table creation and data insertion for the sql server.
I have come across a number of resources listing how to pull data from a datatable object and create the database on the server using SMO (SQL Managemant Objects, i think) But I can't see any resources on how to actually pull the Access data into a Datatable object in the first place. Either way, this method obviously will incur some speed penalties, also maybe some problems with datatypes etc... So finding a way to automate the sql import is by far the better idea!
If anyone has any ideas I would be so grateful!
Thanks a lot.
Ben