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.Wri teAllText( "C:\FasTra x\PreReqs\ ExportFile s\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\Ex portFiles\ ;Hdr=No].[ ZNUMBERS.c sv]", 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
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.Wri
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\
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
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
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was able to find a workaround utilizing a text file as an intermediary.
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)