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

[ryguy7272]

I played with the code here:

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---

 

[ryguy7272]

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

Here’s 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---
(e-mail address removed)