Any sample to connect a remoted database through Excel?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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
 
You can try through an ODBC connection, depending of what DB you try to
connect. After that you only need to configure the connection in the DATA -
CONNECTIONS tab in Office Excel 2007.

Regards.
 
Assuming you have the ODBC drivers and a stable connection to the database
server then you should be able to use MS Query which you access via Data ->
Import External Data -> new Database Query.

That will allow you to read from the database. If you need to write back
then you will need to use VBA code with ADODB Recordsets.
 
Thank everyone very much for any suggestions
Does anyone have any sample for download? therefore, I can see the approach.
I would like to do some coding.
Thank everyone very much for your quick response
Eric
 
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
 
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
 
You need to provide more details on *your exact requirements*.
General questions like this are difficult to address.

Tim
 
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")

'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
 
See below

Tim


Eric said:
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")
Click to expand...

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").value
shtData.cells(i,2).value=rsCommon.Fields("phone").value
i=i+1
rsCommon.movenext
loop
end if
 
Back
Top