The following example uses the bcp functions of Vbsql.ocx to copy the authors table in the pubs database to a file called Authors.sav. For this code example to work you must have the file Vbsql.ocx present and registered on your computer. With a code module open in Microsoft Access, click References on the Tools menu and be sure that Vbsql.ocx is checked in the list of Available References. If the file does not appear in the list, browse for it, and select it.
Some lines of code below exceed the printed page. Such lines use an underscore as a line continuation character.
'Connection declarations
Declare Function SqlLogin Lib "VBSQL.OCX" () As Long
Declare Function SQLSETLUSER Lib "VBSQL.OCX" Alias_
"SqlSetLUser" (ByVal Login As Long, User As String) As Long
Declare Function SqlSetLPwd Lib "VBSQL.OCX" (ByVal Login As_
Long, Pwd As String) As Long
Declare Function SqlSetLApp Lib "VBSQL.OCX" (ByVal Login As_
Long, App As String) As Long
Declare Function SqlOpen Lib "VBSQL.OCX" (ByVal Login As Long,_
Server As String) As Long
'bcp declarations
Declare Function SqlbcpInit Lib "VBSQL.OCX" (ByVal SqlConn As_
Long, TblName As String, HFile As String, ErrFile As String, ByVal_
Direction As Integer) As Long
Declare Function SqlbcpExec Lib "VBSQL.OCX" (ByVal SqlConn_
As Long, RowsCopied As Long) As Long
Declare Function SqlbcpColfmt Lib "VBSQL.OCX" (ByVal SqlConn_
As Long, ByVal FColumn As Long, ByVal FType As Long, ByVal_
FPLen As Long, ByVal FCLen As Long, FTerm As String, ByVal_
FTLen As Long, ByVal TCol As Long) As Long
Declare Function SqlbcpColumns Lib "VBSQL.OCX" (ByVal_
SqlConn As Long, ByVal ColCount As Long) As Long
'Global values for bcp in and out and succeed and fail
Global Const DBIN& = 1 ' transfer from client to server
Global Const DBOUT& = 2 ' transfer from server to client
Global Const SUCCEED& = 1
Global Const FAIL& = 0
'Connection closing declarations
Declare Sub SqlClose Lib "VBSQL.OCX" (ByVal SqlConn As Long)
Declare Sub SqlExit Lib "VBSQL.OCX" ()
Declare Sub SqlWinExit Lib "VBSQL.OCX" ()
Function bcp_out()
Dim Loginrec As Long
Dim SqlConn As Long
Dim Result As Long
Dim RowsCopied As Long
Dim strServer As String
On Error GoTo bcp_out_err
'Specify the login parameters
Loginrec = SqlLogin
strServer = InputBox("Enter the name of your server.""Server_
Name?")
Result = SQLSETLUSER(Loginrec, "sa")
Result = SqlSetLPwd(Loginrec, "")
Result = SqlSetLApp(Loginrec, "AccExamp")
'Open a connection to SQL Server
SqlConn = SqlOpen(Loginrec, strServer)
If SqlConn = 0 Then MsgBox "Failed to open connection to_
server." & Chr$(10) & "Check server name."
'Initialize bcp.
Result = SqlbcpInit(SqlConn, "pubs..authors", "c:\authors.sav",_
"c:\bcperr.txt", DBOUT)
If Result = FAIL Then
MsgBox "bcp Init failed!"
GoTo bcp_out_err
End If
'Execute the bulk-copy.
Result = SqlbcpExec(SqlConn, RowsCopied)
If Result = FAIL Then
MsgBox "Incomplete bulk-copy. Only " & RowsCopied & " rows_
copied."
GoTo bcp_out_err
End If
'Close the connection
SqlClose (SqlConn)
Exit Function
bcp_out_err:
MsgBox "Error " & Err & ": " & Err.Description
'Close the connection
SqlClose (SqlConn)
End Function