Access to SQL 2000

I would like to write a program to convert Access tables to SQL 2000 tables.
I could certainly write a program which collects each field in the access
table and creates a similar field in SQL 2000 and then populates the file,
however I am assuming that XML may offer a solution which would require only
a few statements. Does anyone with XML expertise have an idea of this?

Regards

sabyasachibose
Thu Mar 04 01:46:07 CST 2004

1.Fill the dataset with tables from Acces
2.Create an XML file, and XML Schema from the Dataset using
ds.WriteXml("C:\\Data.xml",System.Data.XmlWriteMode.WriteSchema)
ds.WriteXmlSchema("C:\\Data.xsd")
3.Use SQLXML 3.0 Bulkload functionality, provided via a COM object (xblkld3.dll), this component can be used .NET through COM-intero
SQLXMLBulkLoad3Class bulkLoad = new SQLXMLBulkLoad3Class()
bulkLoad.ConnectionString = txtDBConnStr.Text

//option
bulkLoad.BulkLoad = chkBulkLoad.Checked
bulkLoad.CheckConstraints = chkCheckConstraints.Checked
bulkLoad.ForceTableLock = chkForceTableLock.Checked
bulkLoad.IgnoreDuplicateKeys = chkIgnoreDuplicateKeys.Checked && chkIgnoreDuplicateKeys.Enabled
bulkLoad.KeepIdentity = chkKeepIdentity.Checked
bulkLoad.KeepNulls = chkKeepNulls.Checked
bulkLoad.SchemaGen = chkSchemaGen.Checked
bulkLoad.SGDropTables = chkSGDropTables.Checked
bulkLoad.SGUseID = chkSGUseID.Checked
bulkLoad.XMLFragment = chkXMLFragment.Checked

//Start Bulk Load now..
bulkLoad.Execute(data.xsd,data.xml)

William
Thu Mar 04 01:48:15 CST 2004

Jerry, you can load a Dataset and use its .WriteXML(Pathname:\Filename).to
serialize it. From there, you can use another
DataSet.ReadXml(Pathname:\Filename) and walk through the dataset firing
insert queries at each pass.

however, this is a fugly approach if you have SQL Server. I'd write a DTS
package and if you need to do it on a recurring basis, schedule it as a job
or rerun the package. DTS is very easy to use if this is all you are doing,
it's designed specifically for tasks like this, and it's very fast. ADO.NET
is a super technology and it's very powerful and will allow you to do
transfers, but it's not specifically designed for large scale data transfers
whereas DTS is. If simplicity is your goal, you won't beat DTS.
"Jerry" <JerryOfBorg@Yahoo.com> wrote in message
news:%23dFTr4aAEHA.2600@TK2MSFTNGP09.phx.gbl...
> I would like to write a program to convert Access tables to SQL 2000
tables.
> I could certainly write a program which collects each field in the access
> table and creates a similar field in SQL 2000 and then populates the file,
> however I am assuming that XML may offer a solution which would require
only
> a few statements. Does anyone with XML expertise have an idea of this?
>
> Regards
>
>