sql excel vba connection problem

[alex1404]

hi all,
when i run this code, i would get "run time error -2147467259
automation error unspecified error"
i used debug, and the error accord in oCon.Open
please help me.

[VBA]Sub Connect2SQLXpress()
Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection
oCon.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;" oCon.Open
Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "Select * From Table1"
oRS.Open Range("A1").CopyFromRecordset oRS
oRS.Close
oCon.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
End Sub[/VBA]

[mohanvijay]

Try different connection strings like below (Which works fine for me)

 
"Provider=SQLNCLI;Server=YourServerName;Database=YourDataBaseName;Uid=UserName;Pwd=Password;"

for more connection strings see http://www.connectionstrings.com/

[Norie]

Are you sure the server name is right?

Does it definitely hav ./ at the start?

[alex1404]

Try different connection strings like below (Which works fine for me)

 
"Provider=SQLNCLI;Server=YourServerName;Database=YourDataBaseName;Uid=UserName;Pwd=Password;"

hi,
first, thanks for you replay
second, i had tried u suggestion and a new error accord at the same line as before.
RUN-time error 3706 "application defined or odject defined error"
i have ado 2.8 library attached in my available ref.
also i want to mention that i tried excel feature of exporting external data form sql server and it worked fine.

i want to apologise for my late response, i had a hard week.
thx again.

here is my code:

Sub Connect2SQLXpress2()

Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset

Set oCon = New ADODB.Connection
oCon.ConnectionString = "Provider=SQLNCLI;Server=n13088c;Database=ALEX;"
oCon.Open

Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "Select * From dbo.emp"
oRS.Open

Range("A1").CopyFromRecordset oRS

oRS.Close

oCon.Close

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing

End Sub

[alex1404]

Are you sure the server name is right?

Does it definitely hav ./ at the start?

hi,
first, thanks for you replay
i want to apologise for my late response, i had a hard week.
i posted my update code

[Norie]

Where do you get the latest error?

[alex1404]

hi
oCon.Open - plz see the update code above,

[Norie]

I can't see anything wrong with the code and I've never had an error like that when working with ADO.

I actually thought at first it might be an Excel VBA error, but if it's happening on oRS I can't see how that would be possible.

Which ADO library did you add?

There are 2 with the version number 2.8, one of them is for labelled Mult-Dimensional and that's the one you don't want.

[alex1404]

hi,
i found this code on web, and it's worked like charm!

i'm guessing it because of the provider SQLOLEDB.1
thnks for the help .

good day

Sub Add_Results_Of_ADO_Recordset()
'This was set up using Microsoft ActiveX Data Components version 2.8

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnStart As Range


Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=ALEX;" & _
"Data Source=N13088C"


Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets(1)

With wsSheet
Set rnStart = .Range("A1")
End With


stSQL = "INSERT INTO dbo.emp (id, name, age) " & _
" VALUES (" & _
"'" & Sheet1.Cells(1, 1) & "', " & _
"'" & Sheet1.Cells(1, 2) & "', " & _
"'" & Sheet1.Cells(1, 3) & "')"

Set cnt = New ADODB.Connection

cnt.Open stADO
cnt.Execute(stSQL)

cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub