Any sample to connect a remoted database through Excel?
See below
Tim
"Eric" wrote in message
...
Thank everyone very much for suggestions
Once I connect a data base through following code,
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
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")
dim i, shtData
i=2
set shtData = this workbook.sheets("Members")
rsCommon.Open "select t.name, t.phone from member t", adoCon
if not rsCommon.EOF then
do while not rsCommon.EOF
shtData.cells(i,1).value=rsCommon.Fields("name").v alue
shtData.cells(i,2).value=rsCommon.Fields("phone"). value
i=i+1
rsCommon.movenext
loop
end if
Set rsCommon = Nothing
End Sub
Sub closeDatabase()
If adoCon Is Not Nothing Then
adoCon.Close
Set adoCon = Nothing
End If
End Sub
"Tim Williams" wrote:
You need to provide more details on *your exact requirements*.
General questions like this are difficult to address.
Tim
"Eric" wrote in message
...
Thank everyone very much for suggestions
Do you mean that I cannot connect ODBC through http?
As I remember that IP address, web site, username, password are given
in
order to make any connection.
Could you please give me more describtion?
Thank you very much for your suggestion
Eric
"Tim Williams" wrote:
Not sure either ODBC or OLE work over http (since you mention a
URL...)
Also - more details: what type of database, where hosted, VPN , etc ?
Tim
"Eric" wrote in message
...
Does anyone have any sample to connect a remoted database through
Excel?
I need to make a connection with a database in China, and I locate
in
HK.
Could anyone give me any suggestions on what I need to retrieve any
data
from
a database?
What I can think of is
URL, username, password, ...
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
|