Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connection using ADO
I have some problems using ADO to retrieve data from an Interbase (.gdb
file) in Visual Basic 6.0. I am trying to connect excel speedsheet to interbase but could not. Here are some parts of the coding involving the database connection: ' Database information DBName = ThisWorkbook.Path & "\t6gl.gdb" ' Open the connection Set Connect = New ADODB.Connection Cnct = "Provider=MSDASQL.1;" Cnct = Cnct & "Data Source=" & DBName & ";" Connect.Open ConnectionString:=Cnct The error message appeared like this: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Driver Manager] Data Source Name not found and no default driver specified. Can someone look at this problem and any solution provided will be highly appreciated. Alvin. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connection using ADO
Hi Alvin,
Try this: Using Excel, try the Import Data option. On the 'Select Data Source' dialog box, click 'New Source'. Go thought all the step to ensure you can connect and retreive data. After you're finished save the datasource file and open it in NotePad. It's in XLM format, but you'll be able to see the connection string used. you can also check out this site: http://www.able-consulting.com/ADO_Conn.htm Hope this helps. Bill -----Original Message----- I have some problems using ADO to retrieve data from an Interbase (.gdb file) in Visual Basic 6.0. I am trying to connect excel speedsheet to interbase but could not. Here are some parts of the coding involving the database connection: ' Database information DBName = ThisWorkbook.Path & "\t6gl.gdb" ' Open the connection Set Connect = New ADODB.Connection Cnct = "Provider=MSDASQL.1;" Cnct = Cnct & "Data Source=" & DBName & ";" Connect.Open ConnectionString:=Cnct The error message appeared like this: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Driver Manager] Data Source Name not found and no default driver specified. Can someone look at this problem and any solution provided will be highly appreciated. Alvin. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connection using ADO
Hi Alvin:
I use the following to connect to Access from Excel. Substtide your path ect and you should get hooked up. If you need the rest of the code to retrive the recordset post back. 'To use ADO objects in an application add a reference 'to the ADO component. From the VBA window select 'Tools/References< check the box ' "Microsoft ActiveX Data Objects 2.x Library" 'You should fully quality the path to your file Dim db_Name As String db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb") Dim DB_CONNECT_STRING As String DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "data Source=" & db_Name & ";" & ", , , adConnectAsync;" 'Create the connection Dim cnn As New ADODB.Connection Set cnn = New Connection cnn.Open DB_CONNECT_STRING If cnn.State = adStateOpen Then MsgBox "Welcome to! " & db_Name, vbInformation, "Good Luck TK" End If Good Luck TK -----Original Message----- I have some problems using ADO to retrieve data from an Interbase (.gdb file) in Visual Basic 6.0. I am trying to connect excel speedsheet to interbase but could not. Here are some parts of the coding involving the database connection: ' Database information DBName = ThisWorkbook.Path & "\t6gl.gdb" ' Open the connection Set Connect = New ADODB.Connection Cnct = "Provider=MSDASQL.1;" Cnct = Cnct & "Data Source=" & DBName & ";" Connect.Open ConnectionString:=Cnct The error message appeared like this: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Driver Manager] Data Source Name not found and no default driver specified. Can someone look at this problem and any solution provided will be highly appreciated. Alvin. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connection using ADO
Hi TK,
Me too will be very inetrested to see all of the code that retrieves and questions data from Access *.mdb file. J_J "TK" wrote in message ... Hi Alvin: I use the following to connect to Access from Excel. Substtide your path ect and you should get hooked up. If you need the rest of the code to retrive the recordset post back. 'To use ADO objects in an application add a reference 'to the ADO component. From the VBA window select 'Tools/References< check the box ' "Microsoft ActiveX Data Objects 2.x Library" 'You should fully quality the path to your file Dim db_Name As String db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb") Dim DB_CONNECT_STRING As String DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "data Source=" & db_Name & ";" & ", , , adConnectAsync;" 'Create the connection Dim cnn As New ADODB.Connection Set cnn = New Connection cnn.Open DB_CONNECT_STRING If cnn.State = adStateOpen Then MsgBox "Welcome to! " & db_Name, vbInformation, "Good Luck TK" End If Good Luck TK -----Original Message----- I have some problems using ADO to retrieve data from an Interbase (.gdb file) in Visual Basic 6.0. I am trying to connect excel speedsheet to interbase but could not. Here are some parts of the coding involving the database connection: ' Database information DBName = ThisWorkbook.Path & "\t6gl.gdb" ' Open the connection Set Connect = New ADODB.Connection Cnct = "Provider=MSDASQL.1;" Cnct = Cnct & "Data Source=" & DBName & ";" Connect.Open ConnectionString:=Cnct The error message appeared like this: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Driver Manager] Data Source Name not found and no default driver specified. Can someone look at this problem and any solution provided will be highly appreciated. Alvin. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connection using ADO
"J_J" wrote: Hi TK, Me too will be very inetrested to see all of the code that retrieves and questions data from Access *.mdb file. J_J Hi J_J Try the following: Private Sub CommandButton4_Click() On Error GoTo ErrHandler Dim rg As Range Set rg = ThisWorkbook.Worksheets(2).Range("a1") 'To use ADO objects in an application add a reference 'to the ADO component. From the VBA window select 'Tools/References< check the box ' "Microsoft ActiveX Data Objects 2.x Library" 'You should fully quality the path to your file Dim db_Name As String db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb") Dim DB_CONNECT_STRING As String DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "data Source=" & db_Name & ";" & ", , , adConnectAsync;" 'Create the connection Dim cnn As New ADODB.Connection Set cnn = New Connection cnn.Open DB_CONNECT_STRING 'Create the recordset Dim rs As ADODB.Recordset Set rs = New Recordset 'Determines what records to show Dim strSQL As String strSQL = "SELECT CompanyName, ContactName, City, Country " & _ "FROM Customers ORDER BY CompanyName" 'Retreive the records rs.CursorLocation = adUseClient rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic 'Test to see if we are connected and have records Dim num As Integer num = rs.RecordCount Dim num1 As Integer num1 = rs.Fields.Count If cnn.State = adStateOpen Then MsgBox "Welcome to! " & db_Name & " Records = " & num & " Fields = " & num1, vbInformation, _ "Good Luck TK" Else MsgBox "Sorry. No Data today." End If 'Copy recordset to the range rs.MoveLast rs.MoveFirst rg.CopyFromRecordset rs rg.CurrentRegion.Columns.AutoFit 'close connection cnn.Close Set cnn = Nothing Set rs = Nothing Exit Sub ErrHandler: MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly End Sub Good Luck TK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Connection using ADO
Thank you TK,
I sure will give it a try. But am too busy today... Regards J_J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Internet connection | Excel Discussion (Misc queries) | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
no DDE connection?? | Excel Discussion (Misc queries) | |||
SQL Connection | Excel Programming | |||
DB Connection error | Excel Programming |