紧急~~！！求SQL与Access之间的互导，在线等~！

[terry183]

怎么样将SQL中其中一个数据库里的数据表导出到Access文件？文件名和路径名可以自定义,也就是说如果文件不存在则创建文件然后写文件,如果存在则覆盖原文件；

相反，如何把Access中其中一个数据库里的数据表导入到SQL中？如果SQL中已存在该数据表，则把该表里的数据清空，再写入。

明天中秋了，才遇上这样难的任务~~~高手们，救救啊`！```给出100分了~！！

[terry183]

多谢楼上两位商人的提点~~

我但现在如何才能把dataTable的数据传到ACCESS文件中呢？就在这里卡住了~

[Radar2006]

呵呵，不用这么复杂。
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23709796.html
刚看了这篇受到启发，access里也可以直接查询sql里的数据的。

1.建access数据库
2.连上access直接执行语句如：

select *
into goods
from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods

结构数据都复制过去了。

[terry183]

我现在都把下面那部份改成这样了，请看：

 Public Sub ExchangeDS(ByVal dsDest As DataSet, ByRef dsSource As DataSet)
        dsDest = New DataSet
        Dim dt As DataTable = New DataTable(TableGoods)
        'dsSource = New DataSet
        Dim conn As New SqlConnection("SERVER=(local);database=text;user id=sa;pwd=123456")
        conn.Open()
        Dim SQLtxt As String = ""
        SQLtxt = "select * from goods"
        Dim cmd As New SqlDataAdapter(SQLtxt, conn)
        cmd.Fill(dt)
        dsDest.Tables.Add(TableGoods)

        Dim Barcode As DataColumn = New DataColumn("Barcode", Type.GetType

("System.String"))
        Dim Named As DataColumn = New DataColumn("Named", Type.GetType("System.String"))
        Dim Unit As DataColumn = New DataColumn("Unit", Type.GetType("System.String"))
        Dim Kind As DataColumn = New DataColumn("Kind", Type.GetType("System.String"))
        Dim Price As DataColumn = New DataColumn("Price", Type.GetType("System.Double"))
        Dim Remark As DataColumn = New DataColumn("Remark", Type.GetType("System.String"))

        Dim cat As ADOX.Catalog = New ADOX.Catalog()
        Dim tbl As New Table
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\ray\NewMDB.mdb;" & 

"Jet OLEDB:Engine Type=5")
        Console.WriteLine("Database Created Successfully")
        tbl.Name = "goods"

        tbl.Columns.Append(Barcode.ToString)
        tbl.Columns.Append(Named.ToString)
        tbl.Columns.Append(Unit.ToString)
        tbl.Columns.Append(Kind.ToString)
        tbl.Columns.Append(Price.ToString)
        tbl.Columns.Append(Remark.ToString)

        Dim A_Barcode As String = ""
        Dim A_Named As String = ""
        Dim A_Unit As String = ""
        Dim A_Kind As String = ""
        Dim A_Price As Double = 0
        Dim A_Remark As String = ""

        For i = 0 To dt.Rows.Count - 1
            A_Barcode = dt.Rows(i)("Barcode").ToString
            A_Named = dt.Rows(i)("Named").ToString
            A_Unit = dt.Rows(i)("Unit").ToString
            A_Kind = dt.Rows(i)("Kind").ToString
            A_Price = dt.Rows(i)("Price").ToString
            A_Remark = dt.Rows(i)("Remark").ToString
        Next i

Dim access As String = "select * into(goods) from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods"
        Dim A_cnn As String = "provider=Microsoft.Jet.OLEDB.4.0;Data 

Source=E:\ray\NewMDB.mdb;Persist Security Info=false"
        Dim A_conn As OleDbConnection = New OleDbConnection(A_cnn)
        A_conn.Open()
        Dim A_com As New OleDbCommand(Access)
        A_com.Connection = A_conn
        A_com.ExecuteNonQuery()
        cat.Tables.Append(tbl)
        A_com.Dispose()
        A_conn.Close()
    End Sub

现在是这里

A_com.ExecuteNonQuery()

报错，说：“select子句包含一个保留字，拼写错误或者丢失的参数，或标点符号不正确，或标点符号不正确”

紧急关头了。。。。真的不懂怎么搞，请高手们帮个忙啊~~~！

[Radar2006]

导入：
insert into [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods
values('kk')

[terry183]

那个。。。“KK”是什么？

[terry183]

按照你的做了，

Dim access As String = "insert * into goods from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods values ('kk')"

还是报错：“insert into 语法有错”

[Radar2006]

我随便建了个goods的表，kk是随便个值。

很简单。

随便找个 access 文件,
新建查询 sql视图
select *
into goods
from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods
就可以看到结果了。

[kingsunmax]

insert into (goods) select * from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods
试试

[terry183]

[Quote=引用 34 楼 kingsunmax 的回复:]

insert into (goods) select * from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods
试试
[/Quote]

这个的语法可以`！（汁，原来之前太急了，自己也没看得到，谢谢提点`！）

但还是出现老问题，A_com.ExecuteNonQuery()报错：“找不到输出表‘goods’”

我真的晕了`~~~

[Radar2006]

select *
into goods
from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=123456].goods

这样才行。

[kingsunmax]

insert into 是针对有表
如果没表就是select into 这样没表就会帮你建一个

[terry183]

如果这样的话，还是回到这里来

VB.NET codeA_com.ExecuteNonQuery()

报错，说：“select子句包含一个保留字，拼写错误或者丢失的参数，或标点符号不正确，或标点符号不正确”


我觉很奇怪啊，我明明就建了这个GOODS的表了啊？怎么说找不到呢？

[Radar2006]

  private void button2_Click(object sender, EventArgs e)
        {
            //建access库。。。

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;Persist Security Info=True");
            string sqlText = @"select * into goods from [ODBC;Driver={SQL Server};Server=.;Database=text;Uid=sa;Pwd=sa].goods";
            OleDbCommand cmd = new OleDbCommand(sqlText, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            conn.Close();
        }

[terry183]

问题解决了~~我知道为什么了~~~

是这一句的顺序问题~~

cat.Tables.Append(tbl)

这句不执行`就还没建立好GOODS表~~

一会派分~！！多谢楼上两位~！

[Radar2006]

cat.Tables.Append(tbl)

还要这句做什么呀
select into 已经复制结构和数据了

[gh_li]

 Protected Function OutToExcel(ByVal dtSource As DataTable, ByVal strSavePath As String) As Boolean
        Try
            Dim xlApp As New Excel.Application
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
            Dim rowIndex, colIndex As Integer
            Dim intSheetCnt As Integer = 0 'sheet的个数
            Dim intRecordsPerSheet As Integer = 65535 '各个Sheet的记录数
            rowIndex = 1
            colIndex = 0
            xlBook = xlApp.Workbooks().Add

            xlSheet = xlBook.Worksheets("sheet1")

            Dim Col As DataColumn
            Dim row As DataRow

            '赋列名
            For Each Col In dtSource.Columns
                colIndex = colIndex + 1
                xlApp.Cells(1, colIndex) = Col.ColumnName
            Next

            For Each row In dtSource.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                For Each Col In dtSource.Columns
                    colIndex = colIndex + 1
                    xlApp.Cells(rowIndex, colIndex) = row(Col.ColumnName)
                Next

                With xlSheet '设置格式
                    .Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Name = "楷体_GB2312" '设置标题为黑体
                    .Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Bold = True
                    .Range(.Cells(1, 1), .Cells(rowIndex, colIndex)).Borders.LineStyle = 1
                End With
                If rowIndex Mod intRecordsPerSheet = 1 Then '当行数超过每个Sheet的设定记录数时,新建一个Sheet 
                    If intSheetCnt = 0 Then
                        xlSheet.Name = "1 - " + (intRecordsPerSheet).ToString()
                    End If
                    xlSheet = xlBook.Worksheets.Add(, xlSheet) '在xlSheet之后增加一个Sheet,再赋给xlSheet
                    intSheetCnt += 1
                    xlSheet.Name = (intSheetCnt * intRecordsPerSheet + 1).ToString() + "-" + _
                                    ((intSheetCnt + 1) * intRecordsPerSheet).ToString()
                    rowIndex = 1
                    colIndex = 0
                    For Each Col In dtSource.Columns '初始化列名
                        colIndex = colIndex + 1
                        xlApp.Cells(1, colIndex) = Col.ColumnName
                    Next
                    With xlSheet '设置格式
                        .Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Name = "楷体_GB2312" '设置标题为黑体
                        .Range(.Cells(1, 1), .Cells(1, colIndex)).Font.Bold = True
                        .Range(.Cells(1, 1), .Cells(rowIndex, colIndex)).Borders.LineStyle = 1
                    End With
                End If
            Next
            '给最后一个Sheet改名
            xlSheet.Name = (intSheetCnt * intRecordsPerSheet + 1).ToString() + "-" + _
                            (intSheetCnt * intRecordsPerSheet + rowIndex).ToString()

            xlBook.SaveAs(strSavePath)
            xlSheet = Nothing
            xlBook.Close()
            xlApp.Quit()
            xlApp = Nothing
        Catch ex As Exception
            Throw New Exception("输出到Excel出错:" + ex.Message)
            Return False
        End Try
        Return True
    End Function

以前写的，从dataTable到Excel文件，只要提供保存路径和从数据查找出来的Datatable。

[terry183]

[Quote=引用 41 楼 Radar2006 的回复:]
cat.Tables.Append(tbl)

还要这句做什么呀
select into 已经复制结构和数据了
[/Quote]

因为我用select into的时候，说我语法错误，郁闷~！

PS：感谢楼上发上完整的原代码，谢谢。

[terry183]

[Quote=引用 24 楼 Radar2006 的回复:]
你这个算是界面操作吧…………但现在“任务”就是要求做这样的功能，因为做出来了，是给别人使用的。
===================================================================================
可以完全用代码实现。

1.新建一access文件
2.链接到sql要导出的表 table01(用代码链接表自已google，链接完后只需考虑access数据库)
3.access中新建一表 table02
4.access中insert table02 select * from table01
[/Quote]

Insert Goods select * from goods？

对不起，我还有一个任务要求没说，那就是ACCESS中的表名、数据库、字段名，字段属性等都要跟SQL中那个相同，
这样的话，不会有问题么？

PS：十分感谢你的意见~~！

[terry183]

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Data.OleDb
Imports System
Imports ADOX


Public Class Form1
    Private Const TableGoods As String = "goods"
    Private ds As DataSet = Nothing

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

        Dim conn As New SqlConnection("SERVER=(local);database=text;user id=sa;pwd=123456")
        conn.Open()
        Dim SQLtxt As String = ""
        SQLtxt = "select * from goods"
        Dim cmd As New SqlDataAdapter(SQLtxt, conn)
        Dim dsSource As DataSet = New DataSet
        cmd.Fill(dsSource, TableGoods)

        ExchangeDS(ds, dsSource)
        cmd.Dispose()
        conn.Close()
    End Sub

    Public Sub ExchangeDS(ByVal dsDest As DataSet, ByRef dsSource As DataSet)
        dsDest = New DataSet
        dsSource = New DataSet
        Dim dt As DataTable = New DataTable(TableGoods)
        dsDest.Tables.Add(TableGoods)

        Dim A_Barcode As String
        Dim A_Named As String
        Dim A_Unit As String
        Dim A_Kind As String
        Dim A_Price As Double
        Dim A_Remark As String
        Dim i As Integer

        If i <= dt.Rows.Count - 1 Then i = i + 1
        A_Barcode = dt.Rows(i)("Barcode").ToString
        A_Named = dt.Rows(i)("Named").ToString
        A_Unit = dt.Rows(i)("Unit").ToString
        A_Kind = dt.Rows(i)("Kind").ToString
        A_Price = dt.Rows(i)("Price").ToString
        A_Remark = dt.Rows(i)("Remark").ToString

        Dim cat As ADOX.Catalog = New ADOX.Catalog()
        Dim tbl As New Table
        cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\ray\NewMDB.mdb;" & "Jet OLEDB:Engine Type=5")
        Console.WriteLine("Database Created Successfully")
        tbl.Name = "goods"
        Dim Access As String = "INSERT INTO goods (Barcode,Named,Unit,Kind,Price,Remark) VALUES ('" & A_Barcode & "' , '" & A_Named & "' , '" & A_Unit & "' ,'" & A_Kind & "' , " & A_Price & " ,'" & A_Remark & "' )"
        Using A_conn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;SERVER=(local);database=Goods;user id=sa;pwd=123456")
            'A_conn.Open()
            Dim A_com As New OleDbCommand(Access)
            A_com.Connection = A_conn
            cat.Tables.Append(tbl)
            A_com.Dispose()
            A_conn.Close()
        End Using
    End Sub

高手们`请看看我这里的代码有什么问题，运行时，这里报错了：
A_Barcode = dt.Rows(i)("Barcode").ToString ‘在位置0处没有任何行

我调试过，发现
Dim dt As DataTable = New DataTable(TableGoods)
dsDest.Tables.Add(TableGoods)
这里的“goods”没有什么数据，到底为什么？


希望各位不要慊我麻烦~~~谢谢`！

[terry183]

说清楚一点，就是
Dim dt As DataTable = New DataTable(TableGoods)

这里的“DT”已经为空了，到底是为什么啊~~~？是哪里错了？