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" <(E-Mail Removed)> wrote in message
news:0FE7559F-7ACB-4ED5-AA21-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:0FE7559F-7ACB-4ED5-AA21-(E-Mail Removed)...
> 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...sts.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 here:

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


HTH,
JP

On May 9, 8:20 pm, Eric <E...@discussions.microsoft.com> 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 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...sts.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/