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
If sqlConnection.State = ConnectionState.Open Then
MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _