VBA SQL

  • Thread starter Thread starter Jack Jacobs
  • Start date Start date
J

Jack Jacobs

Hi,

Although not completely new, I am a newbie to programming concerning
SQL-server.

Right now I'm using an access-database to store my data in. I'm using Excel
VBA to access and feed the database. (I need excel for easy manipulation of
the data). But it's all getting too large and too slow. So I should be using
SQL-server instead.

But I have no idea where to start. Searching the net gives some samples, but
no clear explantion.

Can somebody please enlighten me on how to connect to a SQL server?
Preferably with a link that explains clearly how and why? I'm using XP
clients, Office 2003 and connect to SQL2000 (on a SBS-server). Pretty
plainforward, I think.

Thanks in advance.

Jack Jacobs
 
Jack,

Use Access's built-in upsizing wizard (in Tools > Database Utilities).
It would hardly get any easier.

HTH,
Nikos
 
Nikos,

I know the wizard. That's not the problem. I'm looking for example code on
how to connect to SQLserver in VBA (excel).

Thanks for your input!

Greetz,

Jack Jacobs
 
Here's an example that will fill a column in a worksheet with the names of
employees from the SQL Server version of the Northwind sample database ...

Private Sub Worksheet_Activate()

Dim connectionString As String
Dim connection As ADODB.connection
Dim rst As ADODB.Recordset
Dim intRow As Integer

connectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Northwind;" & _
"Data Source=(local)"
Set connection = New ADODB.connection
connection.Open connectionString
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = connection
.Source = "SELECT * FROM Employees"
.Open
Do Until .EOF
intRow = intRow + 1
Me.Cells(intRow, 1) = .Fields("LastName") & ", " &
..Fields("FirstName")
.MoveNext
Loop
.Close
End With
connection.Close

End Sub
 
Thanks Brendan,

It may sound real easy when you know it! Replacing (local) with my
servername and it worked!

You made me a happy man!

Jack Jacobs
 
Back
Top