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

            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)

            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 & ",")
                str.Replace(",", vbCrLf, str.Length - 1, 1)

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


            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)

            MsgBox("Z Data updated successfully.", vbInformation, "Success!")
        Catch ex As Exception
            If accConnection.State = ConnectionState.Open Then
            End If
            If sqlConnection.State = ConnectionState.Open Then
            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