Connection using ADO

[alvin]

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.

[Bill]

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.
>
>
>
>.
>

[TK]

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.
> >
> >

[J_J]

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" <TK@discussions.microsoft.com> wrote in message
news:6517BBE6-6C13-4008-B9E3-A01D8F2E6E12@microsoft.com...
> 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.
>> >
>> >
>
>

[TK]

"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

[J_J]

Thank you TK,
I sure will give it a try. But am too busy today...
Regards
J_J