Question about ADODB

[David Gray]

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.

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.movenext 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.Connection
Dim adoRecordset As ADODB.Recordset
Dim connectString As String

CommonDialog1.ShowOpen

If CommonDialog1.FileName <> "" Then

Set adoConnection = New ADODB.Connection
Set adoRecordset = New ADODB.Recordset
Let connectString = "Provider=microsoft.jet.oledb.3.51;" & _
"Data Source=" & CommonDialog1.FileName
End If

Debug.Print connectString

adoConnection.Open connectString
'adoRecordset.Open "Publishers", adoConnection
adoRecordset.Open "Publishers", adoConnection

Do Until adoRecordset.EOF
List1.AddItem adoRecordset!Name
adoRecordset.MoveNext

Loop

adoRecordset.Close
adoConnection.Close
Set adoRecordset = Nothing
Set adoConnection = Nothing

[Bob Butler]

"David Gray" <bl**@blah.com> wrote in message
news:mc********************************@4ax.com

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.

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
ActiveConnection property to your ADODB.Connection object; you can then get
the Tables collection from that and then Keys and Columns collections along
with related properties

[Jeff North]

On Mon, 01 Sep 2003 21:35:37 +0100, in comp.lang.visual.basic David
Gray <bl**@blah.com> wrote:

| Greetings all,
|
| How can I use ADODB to return all tables in an access DB chosen by the
| user?
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.
| 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.movenext 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.Connection
| Dim adoRecordset As ADODB.Recordset
| Dim connectString As String
|
| CommonDialog1.ShowOpen
|
| If CommonDialog1.FileName <> "" Then
|
| Set adoConnection = New ADODB.Connection
| Set adoRecordset = New ADODB.Recordset
|
|
| Let connectString = "Provider=microsoft.jet.oledb.3.51;" & _
| "Data Source=" & CommonDialog1.FileName
| End If
|
| Debug.Print connectString
|
| adoConnection.Open connectString
| 'adoRecordset.Open "Publishers", adoConnection
| adoRecordset.Open "Publishers", adoConnection
|
| Do Until adoRecordset.EOF
| List1.AddItem adoRecordset!Name
| adoRecordset.MoveNext
|
| Loop
|
| adoRecordset.Close
| adoConnection.Close
| Set adoRecordset = Nothing
| Set adoConnection = Nothing

---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[David Gray]

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
<jn****@yourpantsbigpond.net.au> wrote:

On Mon, 01 Sep 2003 21:35:37 +0100, in comp.lang.visual.basic David
Gray <bl**@blah.com> wrote:

| Greetings all,
|
| How can I use ADODB to return all tables in an access DB chosen by the
| user?

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.

| 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.movenext 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.Connection
| Dim adoRecordset As ADODB.Recordset
| Dim connectString As String
|
| CommonDialog1.ShowOpen
|
| If CommonDialog1.FileName <> "" Then
|
| Set adoConnection = New ADODB.Connection
| Set adoRecordset = New ADODB.Recordset
|
|
| Let connectString = "Provider=microsoft.jet.oledb.3.51;" & _
| "Data Source=" & CommonDialog1.FileName
| End If
|
| Debug.Print connectString
|
| adoConnection.Open connectString
| 'adoRecordset.Open "Publishers", adoConnection
| adoRecordset.Open "Publishers", adoConnection
|
| Do Until adoRecordset.EOF
| List1.AddItem adoRecordset!Name
| adoRecordset.MoveNext
|
| Loop
|
| adoRecordset.Close
| adoConnection.Close
| Set adoRecordset = Nothing
| Set adoConnection = Nothing

---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[David Gray]

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_Click()
Thanks in advance,

Dave.
Public Sub cmdSelectDB_Click()
Dim t As TableDef
Dim strTableList As String
Dim MsgLine As String
CommonDialog1.Filter = "Access (*.mdb)|*.mdb"
CommonDialog1.DialogTitle = "Select an Access DB to load..."
CommonDialog1.ShowOpen

If CommonDialog1.FileName <> "" Then
Let Data1.DatabaseName = CommonDialog1.FileName

Debug.Print CommonDialog1.FileName

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

Debug.Print CommonDialog1.FileName

Let MsgLine = CommonDialog1.FileName
Let DBFileName = CommonDialog1.FileName
'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" <ti*******@nospam.com>
wrote:

"David Gray" <bl**@blah.com> wrote in message
news:mc********************************@4ax.com

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.

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
ActiveConnection property to your ADODB.Connection object; you can then get
the Tables collection from that and then Keys and Columns collections along
with related properties

[David Gray]

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.recordsource to the table name (Again by way of user
selection in a listbox), data1.recordsettype to the table name.

The DataReport1.DataMember = tablename ,

DataReport1.datasource 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.Text = ""
Let SaveOrLoad = "Load"
Let txtReportName.Visible = False
Let List3.Visible = True
Let cmdReportName.Visible = False
Let List3.Visible = True

Call LoadSettings

End Sub

Private Sub cmdReport_Click()
Data1.Refresh

Let Data1.RecordSource = DBTableName

Let Label1.DataMember = DBTableName

Let DataReport1.DataMember = DBTableName
End Sub

Private Sub cmdReportName_Click()
If txtReportName.Text <> "" 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.Text = ""
Let Frame1.Visible = True
Let List3.Visible = False
Let SaveOrLoad = "Save"
End Sub

Public Sub cmdSelectDB_Click()
Dim t As TableDef
Dim strTableList As String
Dim MsgLine As String
CommonDialog1.Filter = "Access (*.mdb)|*.mdb"
CommonDialog1.DialogTitle = "Select an Access DB to load..."
CommonDialog1.ShowOpen

If CommonDialog1.FileName <> "" Then
Let Data1.DatabaseName = CommonDialog1.FileName
Data1.Refresh

'Debug.Print CommonDialog1.FileName

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

Debug.Print CommonDialog1.FileName

Let MsgLine = CommonDialog1.FileName
Let DBFileName = CommonDialog1.FileName
'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:="ReportNames", _
Key:=RepName, setting:=RepName
'Key:="Name", setting:=RepName


Let Junk = DBFileName
SaveSetting appname:="MDBFE", section:=RepName, _
Key:="DBFileName", setting:=DBFileName

SaveSetting appname:="MDBFE", section:=RepName, _
Key:="DBTableName", setting:=DBTableName
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:=RepName, _
Key:=t_Key, setting:=t_setting
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:="ReportNames")

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

Debug.Print intSettings & " " & MySettings(intSettings, 0),
MySettings(intSettings, 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:=ReportName)

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

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

End Select

Next intSettings



End Sub