update sql sever

[Johnny42]

Trying to use this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q321686

to update tables in an sql database:

Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=<test>;Database=<PerezData>;" & _
"UID=<myname>;PWD=<123456>].[tblSupplier] " & _
"FROM [tblSupplier$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

getting error....can anyone help ?

 

[PHV]

getting error
Which error message ? Which line highlighted when in debug mode ?
As we are in the Access VBA forum I assume that ActiveWorkbook.FullName should be qualified with the name of the instantited Excel.Application object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244