Prev: remove duplicates
Next: active window on top
From: ryguy7272 on 5 May 2010 15:15

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
From: ryguy7272 on 6 May 2010 13:07
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---
ryguy7272(a)hotmail.com

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


"ryguy7272" wrote:

>
> 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---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
 | 
Pages: 1
Prev: remove duplicates
Next: active window on top