Marcus，


将Access 2000数据库导入SQL Server的最简单方法是使用

Access'的升迁向导。


Kerry Moorman

马库斯写道：

Marcus,

The easiest way to get an Access 2000 database into SQL Server is to use
Access''s Upsizing Wizard.

Kerry Moorman
"Marcus" wrote:

我创建了一个VB.Net 1.1应用程序，它遍历传递给它的任何基本Access 2000数据库中的所有表格，并生成相同的
表格SQL Server Express数据库中的结构。结构良好（从一个到另一个的小数据转换，例如
是/否 - >位，备忘录 - >文本等）。我现在的问题是将数据从Access传输到SQL Server。我认为这将是一个相当直接的过程，但我不认为我想通过那个
，除非我遗漏了一些东西。我目前有代码
一次从Access中检索一个DataTable对象。我现在想到的将数据导入SQL Server的唯一方法是为SQL Server中的每个表创建一个DataAdapter，并为每个适配器创建一个InsertCommand
。这将涉及遍历每个表的所有列，确定它们的数据类型和长度，然后添加参数。这是我必须做的，还是有更短的方法？也许将Access表转储到文件然后使用批量
导入实用程序（bcp）例如???嗯......

感谢您的帮助，
Marcus

***请回复此群组。我不查看这个电子邮件地址***

I created a VB.Net 1.1 application that iterates through all the tables
in any basic Access 2000 database passed to it and generates the same
table structure in a SQL Server Express database. The structure is
created fine (with minor data conversions from one to the other, e.g.
yes/no --> bit, memo --> text, etc). My problem now is transferring the
data over from Access to SQL Server. I thought it would be a fairly
straight forward process, but I don''t think I thought it through that
well, unless I am missing something. I currently have the code
retrieving a DataTable object from Access one at a time. The only way
I can think of now to get the data into SQL Server is to create a
DataAdapter for each table in SQL Server and create an InsertCommand
for each of adapters. This will involve iterating through all the
columns of each table, determing their data types and length, and then
adding the parameters. Is this what I have to do, or is there a shorter
method? Perhaps dump the Access table to a file and then use bulk
import utility (bcp) for example??? Hmmm...

Thanks for any help,
Marcus

*** Please respond to this group. I do not check this email address ***

" Marcus" <豪********** @ hotmail.com> schrieb

"Marcus" <ho**********@hotmail.com> schrieb

我创建了一个VB.Net 1.1应用程序，它遍历传递给它的任何基本Access 2000数据库中的所有表，并在SQL Server中生成相同的表结构快递数据库。
结构创建得很好（从一个到另一个的次要数据转换，例如是/否 - >位，备忘录 - >文本等）。我现在的问题是将数据从Access传输到SQL Server。我认为这将是一个相当直接的过程，但我不认为我通过那个好想，除非我错过了什么。我目前有代码一次从Access中检索一个DataTable对象。我现在想到的唯一方法是将数据导入到SQL Server中，就是为SQL Server中的每个表创建一个DataAdapter，并为每个适配器创建一个InsertCommand。这将涉及遍历每个表的所有列，确定它们的数据类型和长度，然后添加参数。这是我必须做的，还是有更短的方法？也许将Access
表转储到一个文件然后使用批量导入实用程序（bcp）来表示
示例???嗯...

I created a VB.Net 1.1 application that iterates through all the
tables in any basic Access 2000 database passed to it and generates
the same table structure in a SQL Server Express database. The
structure is created fine (with minor data conversions from one to
the other, e.g. yes/no --> bit, memo --> text, etc). My problem now
is transferring the data over from Access to SQL Server. I thought
it would be a fairly straight forward process, but I don''t think I
thought it through that well, unless I am missing something. I
currently have the code retrieving a DataTable object from Access
one at a time. The only way I can think of now to get the data into
SQL Server is to create a DataAdapter for each table in SQL Server
and create an InsertCommand for each of adapters. This will involve
iterating through all the columns of each table, determing their
data types and length, and then adding the parameters. Is this what
I have to do, or is there a shorter method? Perhaps dump the Access
table to a file and then use bulk import utility (bcp) for
example??? Hmmm...

不完全是你想要的，但这是我编写的用于导入整个数据库的（快速和肮脏）代码

。因此，您必须再次调整数据类型
。您还可以更改代码以使用插入

到而不是选择进入仅用于导入数据的语句。


1.添加链接服务器到SQL服务器实例。它是

源数据库的链接。

2.在表单上，​​添加一个名为''txtLog'的多行文本框和一个名为
的按钮
''Button1''。

3.添加名为SqlConnection1的SqlConnection。这是目的地

数据库。

4.插入以下代码。将常量''linkedServer''的值更改为

链接服务器的名称。

Const linkedServer As String =" testMDB"


Private Sub Button1_Click（_

ByVal sender As System.Object，ByVal e As System.EventArgs）_

处理Button1.Click


Dim cmd As SqlCommand

Dim reader As SqlDataReader

Dim tables As New ArrayList


Windows .Forms.Cursor.Current = Cursors.WaitCursor


Me.txtLog.Text = String.Empty


Me.SqlConnection1.Open（）


尝试

''获取所有表格


cmd =新的SqlCommand（" sp_tables_ex"，我。 SqlConnection1）

cmd.CommandType = CommandType.StoredProcedure


使用cmd.Parameters

.Add（" @ table_server"， SqlDbType.NVarChar）.Value = linkedServer

.Add（" @ table_type"，SqlDbType.NVarChar）.Value =" TABLE"

结束


reader = cmd.ExecuteReader（）

尝试

Do while readerRead

tables.Add（读者（TABLE_NAME）。ToString）

循环

最后

读者。关闭（）

结束尝试


''导入所有表格


For each tname As String In tables

Me.txtLog.AppendText（tname& " ..."）

Me.txtLog.SelectionStart = Me.txtLog.Text.Length


尝试

Dim cmd2 As New SqlCommand

Dim count As Integer

cmd2.CommandText =" select * into [" &安培; tname _

& 来自 &安培; linkedServer& &QUOT; ... [&QUOT; &安培; tname& "]"

cmd2.Connection = Me.SqlConnection1

cmd2.CommandTimeout = 3600''60分。


count = cmd2.ExecuteNonQuery

Me.txtLog.AppendText（count.ToString& vbCrLf）

Me.txtLog.SelectionStart = Me.txtLog.Text。长度

Catch ex As Exception

Me.txtLog.AppendText（ex.Message& vbCrLf）

Me.txtLog.SelectionStart = Me。 txtLog.Text.Length

结束尝试

刷新（）

AZ.Win32.PeekMessage（Nothing，Nothing，0,0,0）

下一页

最后

Me.SqlConnection1.Close（）

Windows.Forms.Cursor.Current =游标.Default

结束尝试


结束子

也许有一种更简单（内置）的方式，但是当我写了我想要的代码

来学习如何自己动手。

Armin

Not exactly what you are looking for, but this is the (quick&dirty) code
that I wrote to import the whole database. Therefore you would have to
adjust the data types again. You can also change the code to use "insert
into" instead of "select into" statements to import the data only.

1. Add a "linked server" to the SQL server instance. It''s a link to the
source database.
2. On a Form, add a multiline textbox named ''txtLog'' and a button named
''Button1''.
3. Add an SqlConnection named ''SqlConnection1''. This is the destination
database.
4. Insert the code below. Change the value of the constant ''linkedServer'' to
the name of your linked server.
Const linkedServer As String = "testMDB"

Private Sub Button1_Click( _
ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles Button1.Click

Dim cmd As SqlCommand
Dim reader As SqlDataReader
Dim tables As New ArrayList

Windows.Forms.Cursor.Current = Cursors.WaitCursor

Me.txtLog.Text = String.Empty

Me.SqlConnection1.Open()

Try
''get all tables

cmd = New SqlCommand("sp_tables_ex", Me.SqlConnection1)
cmd.CommandType = CommandType.StoredProcedure

With cmd.Parameters
.Add("@table_server", SqlDbType.NVarChar).Value = linkedServer
.Add("@table_type", SqlDbType.NVarChar).Value = "TABLE"
End With

reader = cmd.ExecuteReader()
Try
Do While reader.Read
tables.Add(reader("TABLE_NAME").ToString)
Loop
Finally
reader.Close()
End Try

''import all tables

For Each tname As String In tables
Me.txtLog.AppendText(tname & "... ")
Me.txtLog.SelectionStart = Me.txtLog.Text.Length

Try
Dim cmd2 As New SqlCommand
Dim count As Integer
cmd2.CommandText = "select * into [" & tname _
& "] from " & linkedServer & "...[" & tname & "]"
cmd2.Connection = Me.SqlConnection1
cmd2.CommandTimeout = 3600 ''60 Min.

count = cmd2.ExecuteNonQuery

Me.txtLog.AppendText(count.ToString & vbCrLf)
Me.txtLog.SelectionStart = Me.txtLog.Text.Length
Catch ex As Exception
Me.txtLog.AppendText(ex.Message & vbCrLf)
Me.txtLog.SelectionStart = Me.txtLog.Text.Length
End Try
Refresh()
AZ.Win32.PeekMessage(Nothing, Nothing, 0, 0, 0)
Next
Finally
Me.SqlConnection1.Close()
Windows.Forms.Cursor.Current = Cursors.Default
End Try

End Sub
Maybe there''s a simpler (built-in) way, but when I wrote the code I wanted
to learn how to do it on my own.
Armin

Marcus，


你们在我们的网站上试过这个样本吗？

http：//www.vb-tips .com / default.aspx？... 5-421ed535c609


我希望这会有所帮助，


Cor >

" Marcus" &LT;豪********** @ hotmail.com&GT; schreef in bericht

news：11 ********************** @ j33g2000cwa.googlegr oups.com ...

Marcus,

Did you try this sample on our website already?

http://www.vb-tips.com/default.aspx?...5-421ed535c609

I hope this helps,

Cor

"Marcus" <ho**********@hotmail.com> schreef in bericht
news:11**********************@j33g2000cwa.googlegr oups.com...

我创建了一个VB.Net 1.1应用程序，它遍历传递给它的任何基本Access 2000数据库中的所有表格，并在SQL Server Express数据库中生成相同的表格结构。结构良好（从一个到另一个的小数据转换，例如
是/否 - >位，备忘录 - >文本等）。我现在的问题是将数据从Access传输到SQL Server。我认为这将是一个相当直接的过程，但我不认为我想通过那个
，除非我遗漏了一些东西。我目前有代码
一次从Access中检索一个DataTable对象。我现在想到的将数据导入SQL Server的唯一方法是为SQL Server中的每个表创建一个DataAdapter，并为每个适配器创建一个InsertCommand
。这将涉及遍历每个表的所有列，确定它们的数据类型和长度，然后添加参数。这是我必须做的，还是有更短的方法？也许将Access表转储到文件然后使用批量
导入实用程序（bcp）例如???嗯......

感谢您的帮助，
Marcus

***请回复此群组。我不查看此电子邮件地址***

I created a VB.Net 1.1 application that iterates through all the tables
in any basic Access 2000 database passed to it and generates the same
table structure in a SQL Server Express database. The structure is
created fine (with minor data conversions from one to the other, e.g.
yes/no --> bit, memo --> text, etc). My problem now is transferring the
data over from Access to SQL Server. I thought it would be a fairly
straight forward process, but I don''t think I thought it through that
well, unless I am missing something. I currently have the code
retrieving a DataTable object from Access one at a time. The only way
I can think of now to get the data into SQL Server is to create a
DataAdapter for each table in SQL Server and create an InsertCommand
for each of adapters. This will involve iterating through all the
columns of each table, determing their data types and length, and then
adding the parameters. Is this what I have to do, or is there a shorter
method? Perhaps dump the Access table to a file and then use bulk
import utility (bcp) for example??? Hmmm...

Thanks for any help,
Marcus

*** Please respond to this group. I do not check this email address ***