Link to home
Start Free TrialLog in
Avatar of fizzlefry
fizzlefry

asked on

Copy 1 small SQL table to MS Access

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Is this a one time deal or something you are going to be repeating?

How about linking to the table in your SQL database, creating a local table in your Access Database with the needed structure, and running an append query to get the data from your linked SQL table into the new Access table?  (No code needed)
Avatar of fizzlefry
fizzlefry

ASKER

It will happen every time the program opens.  I can't link, because the SQL table is ever growing.  I am bringing over only a small piece of that data and then will parse it against another static table in Access, to keep the record number clean and only showing the records that need to be selected.
Hi
What version of SQL Server are you using.
I am thinking that maybe you should look into SSIS.

This would eliminate the need for writing and maintaining a lot of code
SQL 2005 FULL on a few, Express on the majority.  I am not doing any of this work through either program.  I purposely am writing a front end solution for the user to keep them away from the source programs.
ASKER CERTIFIED SOLUTION
Avatar of fizzlefry
fizzlefry

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I was able to find a workaround utilizing a text file as an intermediary.