Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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. -- HTH... Jim Thomlinson "Eric" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Eric" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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. "Eric" escribió en el mensaje de ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any sample to connect a remoted database through Excel?
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 "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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to connect excel(If I consider it as a database) with a websit | Excel Discussion (Misc queries) | |||
Any sample to connect a remoted database through Excel | Excel Discussion (Misc queries) | |||
Need sample code to connect Excel to Mercury Quality Center | Excel Programming | |||
connect to access database and run a query in excel VBA | Excel Discussion (Misc queries) | |||
connect a form to excel database | Excel Discussion (Misc queries) |