Any macro programming sample for accessing database through Excel?

[Eric]

Does anyone have any macro programming sample for accessing database through
Excel? I get url, username, password, anything you can name it.
Does anyone have any sample for sharing?
Thanks in advance for any suggestions
Eric

 

[Tim Williams]

What type of database ?
What type of access: local / remote / http ?

Most example code out there will use ADO or DAO (pretty much
Access-specific), so just google for "VBA ADO" and you'll fing plenty of
examples.

Tim

 

[Norman Jones]

Hi Eric,

See Ole P Erlandsen at:


http://www.erlandsendata.no/english/index.php?t=envbadac

 

[Robert Martim, Excel]

Assuming it is a SQL Server, you can do as follows:

strSQLServerName = "000.000.000.00" 'Name/location or IP address of the SQL
Server
strSQLDBUserName = "Your_UserName" 'User name for SQL Server Authentication
strSQLDBPassword = "Your_Password" 'Ppassword for SQL Server Authentication
strSQLDBName = "your_database_name" 'SQL database name

strCon = "Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName &
";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"


--
Robert
Author of RibbonX: Customizing the Office 2007 Ribbon:
LATEST INTERACTIVE TUTORIAL:
http://www.msofficegurus.com/videos/StepByStep_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/

 

[Eric]

Thank everyone very much for suggestions
Do I need to setup ODBC based on your coding?

Could you please give me one example on retrieving data?
I would like to retrieve the variable "name" and "phone" under the table
"member", and insert into column A for name and column B for phone.
Could you please give me any suggestions?
Thank you very much for your suggestions
Eric

 

[Robert Martim, Excel]

Eric

Here's how you could do it (cross-posted answer here
http://www.msofficegurus.com/forum/forum_posts.asp?TID=17):

Dim adoCon As Object
Dim rsCommon As Object

Sub Variable()
strSQLServerName = "IP_address"
strSQLDBUserName = "Your_SQL_UserName"
strSQLDBPassword = "Your_SQL_Password"
strSQLDBName = "YOur_SQL_DBName"

strCon = "Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName &
";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"

Call openDatabase(strCon)

End Sub

Sub openDatabase(strCon)

strCon = "Provider=SQLOLEDB;Connection Timeout=90;" & strCon
Set adoCon = CreateObject("ADODB.Connection")

On Error Resume Next
adoCon.connectionstring = strCon
adoCon.Open

If Err.Number <> 0 Then MsgBox Err.Description, vbCritical
On Error GoTo 0

'Your recordset, so look for the fields you want using this rs object
Set rsCommon = CreateObject("ADODB.Recordset")

'do your thing with the rs here
Set rsCommon = Nothing

End Sub

Sub closeDatabase()
If adoCon Is Not Nothing Then
adoCon.Close
Set adoCon = Nothing
End If
End Sub


--
Robert
Author of RibbonX: Customizing the Office 2007 Ribbon:
LATEST INTERACTIVE TUTORIAL:
http://www.msofficegurus.com/videos/StepByStep_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/

 

[JP]

I have some sample code here:

http://www.codeforexcelandoutlook.com/DQYFiles.html


HTH,
JP

 

[Eric]

Thank everyone very much for suggestions and your link, I don't know this
forum before.
Eric :>