Question about ADODB

**Bob Butler** · Jul 17 '05, 07:59 PM

Re: Question about ADODB

"David Gray" <blah@blah.co m> wrote in message
news:mcb7lvgf5e udflt0lfkdgftbh ltfj9oc44@4ax.c om[color=blue]
> Greetings all,
>
> How can I use ADODB to return all tables in an access DB chosen by the
> user?
>
> I'm able so far to select the DB file and build up my connect string,
> but I would like to offer a list of tables withing the DB and allow
> the user to choose.[/color]

Use the ADOX objects; under references it'll be listed as something like
'Microsoft ADO Ext. 2.5 for DDL and Security'

That will let you create a new ADOX.Catalog object and set the
ActiveConnectio n property to your ADODB.Connectio n object; you can then get
the Tables collection from that and then Keys and Columns collections along
with related properties

**Jeff North** · Jul 17 '05, 07:59 PM

Re: Question about ADODB

On Mon, 01 Sep 2003 21:35:37 +0100, in comp.lang.visua l.basic David
Gray <blah@blah.co m> wrote:
[color=blue]
>| Greetings all,
>|
>| How can I use ADODB to return all tables in an access DB chosen by the
>| user?[/color]

asp101.com - This website is for sale! - asp101 Resources and Information.

http://www.asp101.com/samples/db_dsn.asp

This website is for sale! asp101.com is your first and best source for all of the information you’re looking for. From general topics to more of what you would expect to find here, asp101.com has it all. We hope you find what you are searching for!

**David Gray** · Jul 17 '05, 07:59 PM

Re: Question about ADODB

Hi,

Thanks for the info. I'm not yet at the .ASP stage, still learning
vanilla VB won't be able to use this.

Cheers,
Dave

On Tue, 02 Sep 2003 01:41:27 GMT, Jeff North
<jnorth@yourpan tsbigpond.net.a u> wrote:
[color=blue]
>On Mon, 01 Sep 2003 21:35:37 +0100, in comp.lang.visua l.basic David
>Gray <blah@blah.co m> wrote:
>[color=green]
>>| Greetings all,
>>|
>>| How can I use ADODB to return all tables in an access DB chosen by the
>>| user?[/color]
>
>http://www.asp101.com/samples/db_dsn.asp
>
>This lists the tables within a selected database.
>Once you click on the table name it lists all the data for the
>selected table.
>Click on an entry an you can edit the data.
>
>Everything that you want but dangerous if the user doesn't know what
>they are doing or understand databases.
>[color=green]
>>| I'm able so far to select the DB file and build up my connect string,
>>| but I would like to offer a list of tables withing the DB and allow
>>| the user to choose.
>>|
>>| Is this possible using MS ActiveX Data Objects 2.0 Library or do I
>>| need to use a a newer version. Also what is the syntax? I have tried
>>|
>>| adorecordset.mo venext or .movefirst but get a parameter error.
>>|
>>| If anyone has a solution or even a code snippet I would be extremely
>>| grateful.
>>|
>>| Cheers,
>>| Dave.
>>|
>>|
>>| Dim adoConnection As ADODB.Connectio n
>>| Dim adoRecordset As ADODB.Recordset
>>| Dim connectString As String
>>|
>>| CommonDialog1.S howOpen
>>|
>>| If CommonDialog1.F ileName <> "" Then
>>|
>>| Set adoConnection = New ADODB.Connectio n
>>| Set adoRecordset = New ADODB.Recordset
>>|
>>|
>>| Let connectString = "Provider=micro soft.jet.oledb. 3.51;" & _
>>| "Data Source=" & CommonDialog1.F ileName
>>| End If
>>|
>>| Debug.Print connectString
>>|
>>| adoConnection.O pen connectString
>>| 'adoRecordset.O pen "Publishers ", adoConnection
>>| adoRecordset.Op en "Publishers ", adoConnection
>>|
>>| Do Until adoRecordset.EO F
>>| List1.AddItem adoRecordset!Na me
>>| adoRecordset.Mo veNext
>>|
>>| Loop
>>|
>>| adoRecordset.Cl ose
>>| adoConnection.C lose
>>| Set adoRecordset = Nothing
>>| Set adoConnection = Nothing[/color]
>
>---------------------------------------------------------------
>jnorth@yourpan tsbigpond.net.a u : Remove your pants to reply
>---------------------------------------------------------------[/color]

**David Gray** · Jul 17 '05, 07:59 PM

Re: Question about ADODB

Hi,

Thanks for the info.

I went away and did some research and now how an almost working
program. I'm using the DATA conrol with no databasename property set.
Giving the user a dialog to choose a DB then doing loops for the
relation names then a loop for the field names.

Problem is the program works if the DataBaseName is set at dsesign
time, but if I blank this field and then assign it a property at run
time I get this...

Object variable or With block variable not set (Error 91)

Has anyone got any ideas? I'm nearly bald with frustration.

Here is my code which fails at

"For Each t In Data1.Database. TableDefs" in the
sub cmdSelectDB_Cli ck()

Thanks in advance,

Dave.

Public Sub cmdSelectDB_Cli ck()
Dim t As TableDef
Dim strTableList As String
Dim MsgLine As String

CommonDialog1.F ilter = "Access (*.mdb)|*.mdb"
CommonDialog1.D ialogTitle = "Select an Access DB to load..."
CommonDialog1.S howOpen

If CommonDialog1.F ileName <> "" Then
Let Data1.DatabaseN ame = CommonDialog1.F ileName

Debug.Print CommonDialog1.F ileName

'C:\Documents and Settings\Dave\D esktop\DB FrontEnd\BIBLIO .MDB
'Let Data1.Connect = "Access"

Debug.Print CommonDialog1.F ileName

Let MsgLine = CommonDialog1.F ileName
Let DBFileName = CommonDialog1.F ileName
'Call OpenDB

For Each t In Data1.Database. TableDefs
If t.Attributes = 0 Then
Let strTableList = t.Name
List1.AddItem strTableList
End If
Next t
End If

End Sub

Private Sub List1_Click()

Dim r As Relation
Dim t As TableDef
Dim i As Index
Dim f As Field

Dim Idx As Long
Dim junk

Let Idx = List1.ListIndex
Let DBTableName = List1.List(Idx)

For Each t In Data1.Database. TableDefs
If t.Name = DBTableName Then
Debug.Print t.Name
'Debug.Print t.Fields
For Each f In t.Fields
Debug.Print "Field: " & f.Name

Let junk = f.Name
List2.AddItem junk

Next f

End If

Next t

End Sub

On Mon, 1 Sep 2003 14:39:40 -0700, "Bob Butler" <tiredofit@nosp am.com>
wrote:
[color=blue]
>"David Gray" <blah@blah.co m> wrote in message
>news:mcb7lvgf5 eudflt0lfkdgftb hltfj9oc44@4ax. com[color=green]
>> Greetings all,
>>
>> How can I use ADODB to return all tables in an access DB chosen by the
>> user?
>>
>> I'm able so far to select the DB file and build up my connect string,
>> but I would like to offer a list of tables withing the DB and allow
>> the user to choose.[/color]
>
>Use the ADOX objects; under references it'll be listed as something like
>'Microsoft ADO Ext. 2.5 for DDL and Security'
>
>That will let you create a new ADOX.Catalog object and set the
>ActiveConnecti on property to your ADODB.Connectio n object; you can then get
>the Tables collection from that and then Keys and Columns collections along
>with related properties[/color]

**David Gray** · Jul 17 '05, 07:59 PM

Help - Binding a DATA control to a Data Report

Greetings all,

2nd dumb question of the day. :-)

I have written a program that allows the user to select an Access
database, then by way of a listbox, a selection of fields and then I
would like to retrieve the data and put in in a Data report.

Problem is I don't know how to do it. :-(

I have dynamically set the DatabaseName via the CommonDialog1 control,
set the data1.recordsou rce to the table name (Again by way of user
selection in a listbox), data1.recordset type to the table name.

The DataReport1.Dat aMember = tablename ,

DataReport1.dat asource DATA1

But no records are displayed.

Can anyone pleeeeeeeease have a look at my code, have a laugh then
tell me what is wrong.

Thanks in advance,
Dave.

Option Explicit
Option Base 1

Private Sub cmdLoadSettings _Click()
Let Frame1.Visible = True
Let Frame1.Caption = "Double click a report to load"
Let txtReportName.T ext = ""
Let SaveOrLoad = "Load"
Let txtReportName.V isible = False
Let List3.Visible = True
Let cmdReportName.V isible = False
Let List3.Visible = True

Call LoadSettings

End Sub

Private Sub cmdReport_Click ()
Data1.Refresh

Let Data1.RecordSou rce = DBTableName

Let Label1.DataMemb er = DBTableName

Let DataReport1.Dat aMember = DBTableName
End Sub

Private Sub cmdReportName_C lick()
If txtReportName.T ext <> "" Then
Let Frame1.Visible = False
End If

If SaveOrLoad = "Save" Then
Call SaveSettings
End If

End Sub

Private Sub cmdReset_Click( )
List1.Clear
List2.Clear
List3.Clear
Frame1.Visible = False
End Sub

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub cmdSaveSettings _Click()
If List1.List(1) = "" Then Exit Sub

Let txtReportName.T ext = ""
Let Frame1.Visible = True
Let List3.Visible = False
Let SaveOrLoad = "Save"
End Sub

Public Sub cmdSelectDB_Cli ck()
Dim t As TableDef
Dim strTableList As String
Dim MsgLine As String

CommonDialog1.F ilter = "Access (*.mdb)|*.mdb"
CommonDialog1.D ialogTitle = "Select an Access DB to load..."
CommonDialog1.S howOpen

If CommonDialog1.F ileName <> "" Then
Let Data1.DatabaseN ame = CommonDialog1.F ileName
Data1.Refresh

'Debug.Print CommonDialog1.F ileName

'C:\Documents and Settings\Dave\D esktop\DB FrontEnd\BIBLIO .MDB
'Let Data1.Connect = "Access"

Debug.Print CommonDialog1.F ileName

Let MsgLine = CommonDialog1.F ileName
Let DBFileName = CommonDialog1.F ileName
'Call OpenDB

For Each t In Data1.Database. TableDefs
If t.Attributes = 0 Then
Let strTableList = t.Name
List1.AddItem strTableList
End If
Next t
End If

End Sub

Public Sub OpenDB()
Dim Junk
Dim strTableList As String
Dim t As TableDef

Let Junk = 1
For Each t In Data1.Database. TableDefs
If t.Attributes = 0 Then

strTableList = t.Name
List1.AddItem strTableList

End If
Next t

End Sub

Private Sub Command1_Click( )
Data1.Recordset .MoveNext
'let data1.Caption =

End Sub

Private Sub Data1_Validate( Action As Integer, Save As Integer)
Data1.Recordset .MoveNext
End Sub

Private Sub Form_Load()
Frame1.Visible = False
End Sub

Private Sub List1_Click()

Dim r As Relation
Dim t As TableDef
Dim i As Index
Dim f As Field

Dim Idx As Long
Dim Junk

List2.Clear

Let Idx = List1.ListIndex
Let DBTableName = List1.List(Idx)

For Each t In Data1.Database. TableDefs
If t.Name = DBTableName Then
'Debug.Print t.Name
'Debug.Print t.Fields
For Each f In t.Fields
Debug.Print "Field: " & f.Name

Let Junk = f.Name
List2.AddItem Junk

Next f

End If

Next t

End Sub

Public Sub SaveSettings()
Dim Junk
Dim RepName
Dim Idx As Long
Dim t_Key As String
Dim t_setting As String

Let RepName = txtReportName

' Save the report names in a section by themselves to enable
recovery of report names.
SaveSetting appname:="MDBFE ", section:="Repor tNames", _
Key:=RepName, setting:=RepNam e
'Key:="Name", setting:=RepNam e

Let Junk = DBFileName
SaveSetting appname:="MDBFE ", section:=RepNam e, _
Key:="DBFileNam e", setting:=DBFile Name

SaveSetting appname:="MDBFE ", section:=RepNam e, _
Key:="DBTableNa me", setting:=DBTabl eName

Let Idx = 0
Do
If List2.List(Idx) = "" Then Exit Do

If List2.Selected( Idx) = True Then

Let t_Key = "LIST2(" & Str(Idx) & ")"
Let t_setting = List2.List(Idx)

SaveSetting appname:="MDBFE ", section:=RepNam e, _
Key:=t_Key, setting:=t_sett ing
End If

Let Idx = Idx + 1
Loop

End Sub

Public Sub LoadSettings()
Dim RepName
Dim Junk As String
'Dim intSettings As Long
'Dim MySettings As Variant, intSettings As Integer

MySettings = GetAllSettings( appname:="MDBFE ",
section:="Repor tNames")

For intSettings = LBound(MySettin gs, 1) To UBound(MySettin gs, 1)
'
Let Junk = MySettings(intS ettings, 1)
List3.AddItem Junk

Debug.Print intSettings & " " & MySettings(intS ettings, 0),
MySettings(intS ettings, 1)
Next intSettings

End Sub

Private Sub List3_Click()

Dim Idx As Long
Dim ReportName, RepName As String
Dim Junk
Dim t_Key As String
Dim t_Value As String

Let Idx = List3.ListIndex
Let ReportName = List3.List(Idx)

MySettings = GetAllSettings( appname:="MDBFE ", section:=Report Name)

For intSettings = LBound(MySettin gs, 1) To UBound(MySettin gs, 1)
'
Let t_Key = MySettings(intS ettings, 0)
Let t_Value = MySettings(intS ettings, 1)

Select Case t_Key
Case "DBFileName "
Let DBFileName = t_Value
Let MsgLine = t_Value
Case "DBTableNam e"
Let DBTableName = t_Value
List1.AddItem t_Value
Case Else
List2.AddItem t_Value

End Select

Next intSettings

End Sub

Question about ADODB

Question about ADODB

Comment

Comment

Comment

Comment

Comment