By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

ADOX error

P: n/a
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.Connection
Dim cmd1 As ADODB.Command
Dim cat1 As ADOX.Catalog
Dim v As ADOX.View
Set cnn1 = New ADODB.Connection
Set cmd1 = New ADODB.Command
Set cat1 = New ADOX.Catalog

Set cmd1.ActiveConnection = CurrentProject.Connection
Debug.Print CurrentProject.Connection ' OK
'cmd1.CommandType = adCmdText
cmd1.CommandText = "SELECT * FROM CallRecords_tab"

Set cat1.ActiveConnection = CurrentProject.Connection

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

cat1.Views.Append "NewView", cmd1 ' error 3001 !!

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

End Sub
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
The only thing you are doing differently from what I would is setting the
ActiveConnection 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.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = "SELECT * FROM CallRecords_tab;"

cat1.Views.Append "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.google.c om...
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.Connection
Dim cmd1 As ADODB.Command
Dim cat1 As ADOX.Catalog
Dim v As ADOX.View
Set cnn1 = New ADODB.Connection
Set cmd1 = New ADODB.Command
Set cat1 = New ADOX.Catalog

Set cmd1.ActiveConnection = CurrentProject.Connection
Debug.Print CurrentProject.Connection ' OK
'cmd1.CommandType = adCmdText
cmd1.CommandText = "SELECT * FROM CallRecords_tab"

Set cat1.ActiveConnection = CurrentProject.Connection

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

cat1.Views.Append "NewView", cmd1 ' error 3001 !!

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

End Sub

Nov 12 '05 #2

P: n/a
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*******************@news-server.bigpond.net.au>...
The only thing you are doing differently from what I would is setting the
ActiveConnection 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.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = "SELECT * FROM CallRecords_tab;"

cat1.Views.Append "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.google.c om...
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.Connection
Dim cmd1 As ADODB.Command
Dim cat1 As ADOX.Catalog
Dim v As ADOX.View
Set cnn1 = New ADODB.Connection
Set cmd1 = New ADODB.Command
Set cat1 = New ADOX.Catalog

Set cmd1.ActiveConnection = CurrentProject.Connection
Debug.Print CurrentProject.Connection ' OK
'cmd1.CommandType = adCmdText
cmd1.CommandText = "SELECT * FROM CallRecords_tab"

Set cat1.ActiveConnection = CurrentProject.Connection

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

cat1.Views.Append "NewView", cmd1 ' error 3001 !!

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

End Sub

Nov 12 '05 #3

P: n/a
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.google.c om...
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*******************@news-server.bigpond.net.au>...
The only thing you are doing differently from what I would is setting the ActiveConnection 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.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = "SELECT * FROM CallRecords_tab;"

cat1.Views.Append "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.google.c om...
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.Connection
Dim cmd1 As ADODB.Command
Dim cat1 As ADOX.Catalog
Dim v As ADOX.View
Set cnn1 = New ADODB.Connection
Set cmd1 = New ADODB.Command
Set cat1 = New ADOX.Catalog

Set cmd1.ActiveConnection = CurrentProject.Connection
Debug.Print CurrentProject.Connection ' OK
'cmd1.CommandType = adCmdText
cmd1.CommandText = "SELECT * FROM CallRecords_tab"

Set cat1.ActiveConnection = CurrentProject.Connection

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

cat1.Views.Append "NewView", cmd1 ' error 3001 !!

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

End Sub

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.