Connecting to a Microsoft SQL database with VBA

[kiwidancer]

Hello, I am using VBA for the first time and am trying to connect to my SQL database to create a report in Excel. And am receiving this error when i set oCon to a new ADOBD connection:
run-time error -2147024770 (8007007e)
automation error
the specified module could not be found

I do have microsoft activex direct object library checked in my reference list

my code is as follows:

Sub enterData()

Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection

oCon.ConnectionString = "Server=servidor\celupal55;Database=celupal55;User ID=;Password=;Trusted_Connection=False;"
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

 

[SkipVought]

Hi,

Have you tried, as a start, using MS Query in Excel. You will need no VBA and you ought to be able to connect to your SQL Server database, if you have an ODBC driver configured.

So, if you can get THAT to work, then the next step would be to try the ADODB method.

Also check you sonnection string at

http://www.connectionstrings.com/

or

http://www.carlprothman.net/Default.aspx?tabid=81

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]