ADOX error

[KemperR]

Hello Experts outhere,

may be someone can tell me whats going wrong with my ADOX trial.
I have an Access 2002 database with some tables and queries (views)

The code listed below works well up to the point where I want to add
the new view to the views collection. I get Runtime error 3001 which
is telling me
"Arguments are of wrong type,are out of acceptable range or conflict
with one another"
I should metion that the table I'm refering is a LINKED one (DB is
also an access 2002 db on the same machine). But I don't expect any
impact by that.

The listing of the views alreay existing (debug.print) works well,
menas the cnn1 and cat1 objects ar probaly OK.

Any idea is welcome

Rolf

Private Sub Command41_Click ()

Dim cnn1 As ADODB.Connectio n
Dim cmd1 As ADODB.Command
Dim cat1 As ADOX.Catalog
Dim v As ADOX.View
Set cnn1 = New ADODB.Connectio n
Set cmd1 = New ADODB.Command
Set cat1 = New ADOX.Catalog

Set cmd1.ActiveConn ection = CurrentProject. Connection
Debug.Print CurrentProject. Connection ' OK
'cmd1.CommandTy pe = adCmdText
cmd1.CommandTex t = "SELECT * FROM CallRecords_tab "

Set cat1.ActiveConn ection = CurrentProject. Connection

For Each v In cat1.Views
Debug.Print v.Name ' OK
Next

cat1.Views.Appe nd "NewView", cmd1 ' error 3001 !!

For Each v In cat1.Views
Debug.Print v.Name
Next

End Sub

[Allen Browne]

Re: ADOX error

The only thing you are doing differently from what I would is setting the
ActiveConnectio n of the command object to something other than the instance
of the catalog you have declared. Try:

Private Sub Command41_Click ()
Dim cat1 As ADOX.Catalog
Dim cmd1 As ADODB.Command

Set cat1 = New ADOX.Catalog
Set cmd1 = New ADODB.Command

Set cat1.ActiveConn ection = CurrentProject. Connection
cmd1.CommandTex t = "SELECT * FROM CallRecords_tab ;"

cat1.Views.Appe nd "NewView", cmd1
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"KemperR" <KemperR@t-online.de> wrote in message
news:90d5732b.0 309041206.1e0cc b25@posting.goo gle.com...[color=blue]
> Hello Experts outhere,
>
> may be someone can tell me whats going wrong with my ADOX trial.
> I have an Access 2002 database with some tables and queries (views)
>
> The code listed below works well up to the point where I want to add
> the new view to the views collection. I get Runtime error 3001 which
> is telling me
> "Arguments are of wrong type,are out of acceptable range or conflict
> with one another"
> I should metion that the table I'm refering is a LINKED one (DB is
> also an access 2002 db on the same machine). But I don't expect any
> impact by that.
>
> The listing of the views alreay existing (debug.print) works well,
> menas the cnn1 and cat1 objects ar probaly OK.
>
> Any idea is welcome
>
> Rolf
>
> Private Sub Command41_Click ()
>
> Dim cnn1 As ADODB.Connectio n
> Dim cmd1 As ADODB.Command
> Dim cat1 As ADOX.Catalog
> Dim v As ADOX.View
> Set cnn1 = New ADODB.Connectio n
> Set cmd1 = New ADODB.Command
> Set cat1 = New ADOX.Catalog
>
> Set cmd1.ActiveConn ection = CurrentProject. Connection
> Debug.Print CurrentProject. Connection ' OK
> 'cmd1.CommandTy pe = adCmdText
> cmd1.CommandTex t = "SELECT * FROM CallRecords_tab "
>
> Set cat1.ActiveConn ection = CurrentProject. Connection
>
> For Each v In cat1.Views
> Debug.Print v.Name ' OK
> Next
>
> cat1.Views.Appe nd "NewView", cmd1 ' error 3001 !!
>
> For Each v In cat1.Views
> Debug.Print v.Name
> Next
>
> End Sub[/color]

[KemperR]

Re: ADOX error =&gt; Multiple user environment and dynamic views

Allen,
thanks a lot for the tip. Its working now! But this leads to a very
general question for me.
Imagine I have a shared DB or the DB is on a server and will be
instanciated several times.

In my undestanding the newly created view is visble to all users.

In case the name of the view is always the same and just the cmd
string will change entries can be overwritten by the users which are
loged in on the same access db.

Can I create private views in memory or such things ?

My target is to create some SQL statement execute it and present it to
the user like opening an existing query.

Ok, I can execute a command and assign the result to a recordset. But
how to make the recordset visible like running a query. Creating a
dynamic form with all the fields seams to be a big task. Or is there
some approch to do that in an easy way?


What is the best way to achieve that functionality ?


Thanks a lot for your help

Rolf



"Allen Browne" <abrowne1_SpamT rap@bigpond.net .au> wrote in message news:<P6R5b.836 09$bo1.46616@ne ws-server.bigpond. net.au>...[color=blue]
> The only thing you are doing differently from what I would is setting the
> ActiveConnectio n of the command object to something other than the instance
> of the catalog you have declared. Try:
>
> Private Sub Command41_Click ()
> Dim cat1 As ADOX.Catalog
> Dim cmd1 As ADODB.Command
>
> Set cat1 = New ADOX.Catalog
> Set cmd1 = New ADODB.Command
>
> Set cat1.ActiveConn ection = CurrentProject. Connection
> cmd1.CommandTex t = "SELECT * FROM CallRecords_tab ;"
>
> cat1.Views.Appe nd "NewView", cmd1
> End Sub
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
>
> "KemperR" <KemperR@t-online.de> wrote in message
> news:90d5732b.0 309041206.1e0cc b25@posting.goo gle.com...[color=green]
> > Hello Experts outhere,
> >
> > may be someone can tell me whats going wrong with my ADOX trial.
> > I have an Access 2002 database with some tables and queries (views)
> >
> > The code listed below works well up to the point where I want to add
> > the new view to the views collection. I get Runtime error 3001 which
> > is telling me
> > "Arguments are of wrong type,are out of acceptable range or conflict
> > with one another"
> > I should metion that the table I'm refering is a LINKED one (DB is
> > also an access 2002 db on the same machine). But I don't expect any
> > impact by that.
> >
> > The listing of the views alreay existing (debug.print) works well,
> > menas the cnn1 and cat1 objects ar probaly OK.
> >
> > Any idea is welcome
> >
> > Rolf
> >
> > Private Sub Command41_Click ()
> >
> > Dim cnn1 As ADODB.Connectio n
> > Dim cmd1 As ADODB.Command
> > Dim cat1 As ADOX.Catalog
> > Dim v As ADOX.View
> > Set cnn1 = New ADODB.Connectio n
> > Set cmd1 = New ADODB.Command
> > Set cat1 = New ADOX.Catalog
> >
> > Set cmd1.ActiveConn ection = CurrentProject. Connection
> > Debug.Print CurrentProject. Connection ' OK
> > 'cmd1.CommandTy pe = adCmdText
> > cmd1.CommandTex t = "SELECT * FROM CallRecords_tab "
> >
> > Set cat1.ActiveConn ection = CurrentProject. Connection
> >
> > For Each v In cat1.Views
> > Debug.Print v.Name ' OK
> > Next
> >
> > cat1.Views.Appe nd "NewView", cmd1 ' error 3001 !!
> >
> > For Each v In cat1.Views
> > Debug.Print v.Name
> > Next
> >
> > End Sub[/color][/color]

[Allen Browne]

Re: ADOX error =&gt; Multiple user environment and dynamic views

Are you aware of the technique of splitting a database into back end (tables
only) and front end (separate mdb file containing the queries, form,
reports, and code, and linked tables)?

Each user gets their own copy of the front end, so you can do whatever you
like: the objects (including views) are completely independent of other
users.

Personally I do not permit the user to open tables and queries/view
directly. Forms are the appropriate interface. A form in Datasheet view
looks the same kind of interface as a query, but gives you much more control
(through the events). You can assign any SQL statement directly to the
RecordSource of the form and you don't need to create a view.

It is also possible to assign a recordset to the form, but I don't see the
advantage in that for the case you describe.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"KemperR" <KemperR@t-online.de> wrote in message
news:90d5732b.0 309050221.48710 542@posting.goo gle.com...[color=blue]
> Allen,
> thanks a lot for the tip. Its working now! But this leads to a very
> general question for me.
> Imagine I have a shared DB or the DB is on a server and will be
> instanciated several times.
>
> In my undestanding the newly created view is visble to all users.
>
> In case the name of the view is always the same and just the cmd
> string will change entries can be overwritten by the users which are
> loged in on the same access db.
>
> Can I create private views in memory or such things ?
>
> My target is to create some SQL statement execute it and present it to
> the user like opening an existing query.
>
> Ok, I can execute a command and assign the result to a recordset. But
> how to make the recordset visible like running a query. Creating a
> dynamic form with all the fields seams to be a big task. Or is there
> some approch to do that in an easy way?
>
>
> What is the best way to achieve that functionality ?
>
>
> Thanks a lot for your help
>
> Rolf
>
>
>
> "Allen Browne" <abrowne1_SpamT rap@bigpond.net .au> wrote in message[/color]
news:<P6R5b.836 09$bo1.46616@ne ws-server.bigpond. net.au>...[color=blue][color=green]
> > The only thing you are doing differently from what I would is setting[/color][/color]
the[color=blue][color=green]
> > ActiveConnectio n of the command object to something other than the[/color][/color]
instance[color=blue][color=green]
> > of the catalog you have declared. Try:
> >
> > Private Sub Command41_Click ()
> > Dim cat1 As ADOX.Catalog
> > Dim cmd1 As ADODB.Command
> >
> > Set cat1 = New ADOX.Catalog
> > Set cmd1 = New ADODB.Command
> >
> > Set cat1.ActiveConn ection = CurrentProject. Connection
> > cmd1.CommandTex t = "SELECT * FROM CallRecords_tab ;"
> >
> > cat1.Views.Appe nd "NewView", cmd1
> > End Sub
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> >
> > "KemperR" <KemperR@t-online.de> wrote in message
> > news:90d5732b.0 309041206.1e0cc b25@posting.goo gle.com...[color=darkred]
> > > Hello Experts outhere,
> > >
> > > may be someone can tell me whats going wrong with my ADOX trial.
> > > I have an Access 2002 database with some tables and queries (views)
> > >
> > > The code listed below works well up to the point where I want to add
> > > the new view to the views collection. I get Runtime error 3001 which
> > > is telling me
> > > "Arguments are of wrong type,are out of acceptable range or conflict
> > > with one another"
> > > I should metion that the table I'm refering is a LINKED one (DB is
> > > also an access 2002 db on the same machine). But I don't expect any
> > > impact by that.
> > >
> > > The listing of the views alreay existing (debug.print) works well,
> > > menas the cnn1 and cat1 objects ar probaly OK.
> > >
> > > Any idea is welcome
> > >
> > > Rolf
> > >
> > > Private Sub Command41_Click ()
> > >
> > > Dim cnn1 As ADODB.Connectio n
> > > Dim cmd1 As ADODB.Command
> > > Dim cat1 As ADOX.Catalog
> > > Dim v As ADOX.View
> > > Set cnn1 = New ADODB.Connectio n
> > > Set cmd1 = New ADODB.Command
> > > Set cat1 = New ADOX.Catalog
> > >
> > > Set cmd1.ActiveConn ection = CurrentProject. Connection
> > > Debug.Print CurrentProject. Connection ' OK
> > > 'cmd1.CommandTy pe = adCmdText
> > > cmd1.CommandTex t = "SELECT * FROM CallRecords_tab "
> > >
> > > Set cat1.ActiveConn ection = CurrentProject. Connection
> > >
> > > For Each v In cat1.Views
> > > Debug.Print v.Name ' OK
> > > Next
> > >
> > > cat1.Views.Appe nd "NewView", cmd1 ' error 3001 !!
> > >
> > > For Each v In cat1.Views
> > > Debug.Print v.Name
> > > Next
> > >
> > > End Sub[/color][/color][/color]