G
GPO
Access 2000.
I am using command text to run the SQL in point 4 below. It is intended to
quickly dump a large amount of data from a text file into a database table.
And it works well, in fact it is blindingly fast compared with all the other
methods I've tried. However I am starting to fear that this SQL syntax is
peculiar only to MS Access (particularly the " IN " & """" & """" & " [Text;
DATABASE=" & strTextFilePath & ";]"). I was hoping that command text, being
ADODB rather than DAO, would require more generic code, but it seems command
text just runs whatever is relevent to the current project's database engine
(which just happens to be jet in this case). My developer guru buddy is
trying to achieve the same functionality and performance in SQL Server that
we have achieved in MS Access.
Is there an equivalent way of directly referencing a text file in an SQL
clause using TSQL?
Regards
GPO
Sub CallTestImport()
Dim strTextFileName As String
Dim strPath As String
Dim strReceivingTable As String
strTextFileName = "MyFile.txt"
strTextFilePath = "C:\DATA\Test_Data"
strReceivingTable = tblMyTable
TestImport strTextFileName, strTextFilePath, strReceivingTable
End Sub
Sub TestImport(strTextFileName As String, strTextFilePath As String,
strReceivingTable)
'This sub uses the ADO command object to perform a bulk insert from a text
file to database table.
Dim cmd As ADODB.Command
'Modify the file name by substituting the dot (.) in file name for a "#".
Dim strTextFileNameModified As String
'Pick the load sequence out of the file name
Dim strLoadSequenceID As String
'1. Modify file name. Syntax requires a "#" instead of a ".".
strTextFileNameModified = Replace(strTextFileName, ".", "#", 1, 1,
vbTextCompare)
'2. Set load sequence - not important to this post.
strLoadSequenceID = Mid(strTextFileName, 8, 1)
'3. Create command object
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
'4. Use SQL as command text. SQL references text file directly
(needs schema.ini). SQL inserts data into specified table.
.CommandText = _
"INSERT INTO " & strReceivingTable & " SELECT * FROM " &
strTextFileNameModified & _
" IN " & """" & """" & " [Text; DATABASE=" & strTextFilePath &
";]"
'5. Run the SQL
.Execute
'6. Use SQL to update the load_sequence_ID field to the load
sequence identifier for this file (not relevant to this post).
.CommandText = _
"UPDATE " & strReceivingTable & " SET Load_Sequence_ID = '" &
strLoadSequenceID & "' " & _
"WHERE Load_Sequence_ID Is Null;"
'7. Run the SQL
.Execute
End With
Set cmd = Nothing
End Sub
I am using command text to run the SQL in point 4 below. It is intended to
quickly dump a large amount of data from a text file into a database table.
And it works well, in fact it is blindingly fast compared with all the other
methods I've tried. However I am starting to fear that this SQL syntax is
peculiar only to MS Access (particularly the " IN " & """" & """" & " [Text;
DATABASE=" & strTextFilePath & ";]"). I was hoping that command text, being
ADODB rather than DAO, would require more generic code, but it seems command
text just runs whatever is relevent to the current project's database engine
(which just happens to be jet in this case). My developer guru buddy is
trying to achieve the same functionality and performance in SQL Server that
we have achieved in MS Access.
Is there an equivalent way of directly referencing a text file in an SQL
clause using TSQL?
Regards
GPO
Sub CallTestImport()
Dim strTextFileName As String
Dim strPath As String
Dim strReceivingTable As String
strTextFileName = "MyFile.txt"
strTextFilePath = "C:\DATA\Test_Data"
strReceivingTable = tblMyTable
TestImport strTextFileName, strTextFilePath, strReceivingTable
End Sub
Sub TestImport(strTextFileName As String, strTextFilePath As String,
strReceivingTable)
'This sub uses the ADO command object to perform a bulk insert from a text
file to database table.
Dim cmd As ADODB.Command
'Modify the file name by substituting the dot (.) in file name for a "#".
Dim strTextFileNameModified As String
'Pick the load sequence out of the file name
Dim strLoadSequenceID As String
'1. Modify file name. Syntax requires a "#" instead of a ".".
strTextFileNameModified = Replace(strTextFileName, ".", "#", 1, 1,
vbTextCompare)
'2. Set load sequence - not important to this post.
strLoadSequenceID = Mid(strTextFileName, 8, 1)
'3. Create command object
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
'4. Use SQL as command text. SQL references text file directly
(needs schema.ini). SQL inserts data into specified table.
.CommandText = _
"INSERT INTO " & strReceivingTable & " SELECT * FROM " &
strTextFileNameModified & _
" IN " & """" & """" & " [Text; DATABASE=" & strTextFilePath &
";]"
'5. Run the SQL
.Execute
'6. Use SQL to update the load_sequence_ID field to the load
sequence identifier for this file (not relevant to this post).
.CommandText = _
"UPDATE " & strReceivingTable & " SET Load_Sequence_ID = '" &
strLoadSequenceID & "' " & _
"WHERE Load_Sequence_ID Is Null;"
'7. Run the SQL
.Execute
End With
Set cmd = Nothing
End Sub