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

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

"Eric" wrote in message
...
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

Hi Eric,

See Ole P Erlandsen at:


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




---
Regards.
Norman


"Eric" wrote in message
...
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

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/...p_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/


"Eric" wrote:

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

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" wrote:

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/...p_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/


"Eric" wrote:

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

Eric

Here's how you could do it (cross-posted answer here
http://www.msofficegurus.com/forum/f...s.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/...p_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/

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

"Robert Martim, Excel" wrote:

Eric

Here's how you could do it (cross-posted answer here
http://www.msofficegurus.com/forum/f...s.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/...p_HT_Chart.asp
FORUM: http://www.msofficegurus.com/forum/

I have some sample code he

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


HTH,
JP

On May 9, 8:20 pm, Eric wrote:
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