How do I use the bcp utility to load text file from MSAccess module
Posted on 2007-03-20
I am importing a text file from my local machine to a SQL Server database on a central network. The data is being imported from MSAccess using the Transfer Text utility. The table that is being loaded is LINKED to the MSAccess application. The import is sooooooo slow. If the table was a local MSAccess table, it's very fast, but the database portion of the application has recently been migrated to SQL Server. I've tried importing the data into a local table and then APPENDING it to the LINKED table, but that's not any faster. Question: Can I use the bcp utility to do the load? Would it be faster? Can I write a VB/VBA module to execute the bcp utility? If so, what is the syntax? The current syntax I have in my VB/VBA module for the IMPORT using Transfer Text is this:
strImportFile = "\\contach-share1\zuserstatistics_" & strMandt & ".txt"
DoCmd.TransferText acImportDelim, "Zuserstatistics Import Spec", "tblUpldzuserstatistics", strImportFile, False, ""