troubleshooting Question

Copy 1 small SQL table to MS Access

Avatar of fizzlefry
fizzlefry asked on
Microsoft AccessVisual Basic.NETMicrosoft SQL Server 2005
6 Comments1 Solution287 ViewsLast Modified:
I am currently trying to move data from an SQL table to a table in MS Access with the same column names (don't ask...)  Anyway, I can't seem to find an easy way to do this.  I can connect to each database, extract data and move it back and forth, but I need to use a flat file (or hand coded CSV) created from SQL.  This is all being done from a form written in visual basic.  My issue is I can create the text file from SQL and move it through to Access, however I need the column names to show up in the SQL flat file.  Presently, there are no column names, so access is calling them F1, F2, and F3.  There are only 3 columns and the flat file is relatively small, only containing a few records (more or less never over 20).  Please see my included code to see how I'm getting there.  

I either need to bring the column names over in the text file, in which I could specify they exist during the import...  OR....  If someone could get me the whole way there without the middleman (that is, moving the data right into Access).  I have (I believe) all the logic / queries to obtain the data.  Just can't get the rest of the way there.  I would really appreciate some insight.

Private Sub UpdateAccFromSQL()


        Dim ds As New DataSet
        Dim sql As String

        Try
            sql = "SELECT REG_NUM, REG_Z_COUNTER, convert(char(10), REG_Z_DATETIME,101) AS REG_Z_DATETIME FROM REGZCNT WHERE (REG_Z_DATETIME BETWEEN '" & StartDate & "' AND '" & EndDate & "')"

            Dim dscmd As New SqlDataAdapter(sql, sqlConnection)
            dscmd.Fill(ds)

            Dim str As New StringBuilder
            Dim dr As DataRow
            Dim column As Object

            For Each dr In ds.Tables(0).Rows
                For Each column In dr.ItemArray
                    str.Append(column.ToString & ",")
                Next
                str.Replace(",", vbCrLf, str.Length - 1, 1)
            Next

            My.Computer.FileSystem.WriteAllText("C:\FasTrax\PreReqs\ExportFiles\ZNUMBERS.csv", str.ToString, False, Encoding.ASCII)
               
            sqlConnection.Close()

            accConnection.Open()


            Dim cmd As New OleDbCommand("DROP TABLE tblCurrentZ", accConnection)
            Dim cmd1 As New OleDbCommand("SELECT * INTO [tblCurrentZ] FROM [Text;Database=C:\FasTrax\PreReqs\ExportFiles\;Hdr=No].[ZNUMBERS.csv]", accConnection)
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            cmd1.ExecuteNonQuery()
            cmd1.Dispose()
            accConnection.Close()


            MsgBox("Z Data updated successfully.", vbInformation, "Success!")
        Catch ex As Exception
            If accConnection.State = ConnectionState.Open Then
                accConnection.Close()
            End If
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
            MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
            & ex.ToString)
        End Try


    End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros