VBA Database access

[Simon Sunke]

Hello

It's been a while since I've been programming in classical VB... (no VB.net)
I now have to write some Excel VBA stuff to access a Database (SQL-Server)
and I don't know how.
Is there any good tutorial or can anybody help me?
Finally, the user must be able to enter a number in the excel sheet and the
macro then gets a record from the database, using the user's input as
WHERE-argument
Thanks for your help!

Simon

 

[Bob Phillips]

Simon,

Here's a simple example of using ADO to access a SQL Server database. You
would need to set all of the connection properties as appropriate, ,and set
the SQL command.

You will also need to set a reference to Microsoft ActiveX Data Objects n.n
Library under Tools/References

Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim iField As Long

Set oConn = New ADODB.Connection
oConn .CursorLocation = adUseClient
oConn .Open "PROVIDER=MSDASQL;driver={SQL Server};" & _
"server=MyServerName;" & _
"uid=MyUID;" & _
"pwd=MyPassword;" & _
"database=MyDatabaseName;"
Set oRS = New ADODB.Recordset
oRS.Open "SELECT * FROM [MyTable] WHERE fieldx = " & myVar, _
oConn, adOpenStatic,adLockOptimistic
ActiveSheet.Cells.Clear
If Not oRS.EOF Then
For iField = 1 To oRS.Fields.Count
Cells(1, iField).Value = oRS.Fields(iField -1).Name
Next
Range("A2").CopyFromRecordset oRS
End If
oRS.Close
Set oRS= Nothing
oConn.Close
Set oConn = Nothing


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jan Karel Pieterse]

Hi Simon,

It's been a while since I've been programming in classical VB... (no VB.net)
I now have to write some Excel VBA stuff to access a Database (SQL-Server)
and I don't know how.
Is there any good tutorial or can anybody help me?
Finally, the user must be able to enter a number in the excel sheet and the
macro then gets a record from the database, using the user's input as
WHERE-argument

Check out this site:

http://www.erlandsendata.no/english/vba/adodao/index.htm

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com