Re: Any sample to connect a remoted database through Excel? by Tim
Tim
Sun May 11 11:11:41 CDT 2008
See below
Tim
"Eric" <Eric@discussions.microsoft.com> wrote in message
news:E0A15D8D-5343-45A1-8510-D78B4A495E8A@microsoft.com...
> 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").value
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" <Eric@discussions.microsoft.com> wrote in message
>> news:89FDD2B4-AA9B-49F0-AC33-9877012C06BC@microsoft.com...
>> > 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" <Eric@discussions.microsoft.com> wrote in message
>> >> news:D96FB62A-57A0-4717-81A4-25F85778AD66@microsoft.com...
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>