Trying to Modify Excel code; Pointing to SQL Server Instead of Acc
 

I played with the code he

http://www.rondebruin.nl/accessexcel.htm

Got that working quick, but now I'm trying to modify the to point to my SQL
Server, and having a heck of a time getting it going.

In Excel, I have set a reference to €˜Microsoft ActiveX Data Objects 2.8
Library

The code in €˜MainMacro is the same. I changed Sub Test4 a bit; now like this:

Sub Test4()
Dim con As New ADODB.Connection
With Sheets("test")
con.Open "Provider=SQLOLEDB;Data Source=LAPTOP\SQL_EXPRESS;Initial
Catalog=;Integrated Security=SSPI;"
GetDataFromAccess "Orders", "ShipCountry", "=", Sheets("test").Range("G6"), _
"ShipVia", "=", Sheets("test").Range("F6"), _
"", "=", "", _
"Freight", "", "100", _
"Freight", "<", "300", _
"", "=", "", _
"", "<=", "", _
Sheets("test").Range("A8"), _
"%", True, True
con.Close
Set con = Nothing
End With
End Sub


When I run the code I get this error: €˜Compile Error; Type Mismatch
This is the line that errors:
"%", True, True

I know the wildcard in SQL Server is the %.
What am I doing wrong?


Thanks, in advance, for the help!!

Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Trying to Modify Excel code; Pointing to SQL Server Instead of Acc
 

I got a bit of help from a friend (thanks Iggy) and finally got this working.

Heres the solution:
In the €˜Examples Module:
Sub Test4()
Dim con As New ADODB.Connection

GetDataFromAccess "Provider=SQLOLEDB;Data Source=LAPTOP\SQL_EXPRESS;Initial
Catalog=Northwind;Integrated Security=SSPI;", "Orders", _
"ShipCountry", "=", Sheets("test").Range("G6"), _
"ShipVia", "=", Sheets("test").Range("F6"), _
"", "=", "", _
"Freight", "=", "", _
"Freight", "=", "", _
"", "=", "", _
"", "<=", "", _
Sheets("test").Range("A8"), _
"*", True, True

End Sub
Also€¦
In the €˜MainMacro Module:
'Create connection string
€˜MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
MyConnection = MyDatabaseFilePathAndName
€¦everything else is the same.

Send me an email if you have any questions.
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:


I played with the code he

http://www.rondebruin.nl/accessexcel.htm

Got that working quick, but now I'm trying to modify the to point to my SQL
Server, and having a heck of a time getting it going.

In Excel, I have set a reference to €˜Microsoft ActiveX Data Objects 2.8
Library

The code in €˜MainMacro is the same. I changed Sub Test4 a bit; now like this:

Sub Test4()
Dim con As New ADODB.Connection
With Sheets("test")
con.Open "Provider=SQLOLEDB;Data Source=LAPTOP\SQL_EXPRESS;Initial
Catalog=;Integrated Security=SSPI;"
GetDataFromAccess "Orders", "ShipCountry", "=", Sheets("test").Range("G6"), _
"ShipVia", "=", Sheets("test").Range("F6"), _
"", "=", "", _
"Freight", "", "100", _
"Freight", "<", "300", _
"", "=", "", _
"", "<=", "", _
Sheets("test").Range("A8"), _
"%", True, True
con.Close
Set con = Nothing
End With
End Sub


When I run the code I get this error: €˜Compile Error; Type Mismatch
This is the line that errors:
"%", True, True

I know the wildcard in SQL Server is the %.
What am I doing wrong?


Thanks, in advance, for the help!!

Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.