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" <Ke*****@t-online.de> wrote in message
news:90******** *************** ***@posting.goo gle.com...
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" <ab************ ***@bigpond.net .au> wrote in message
news:<P6******* ************@ne ws-server.bigpond. net.au>...
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" <Ke*****@t-online.de> wrote in message
news:90******** *************** ***@posting.goo gle.com... 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
