C
Chuck
Hi,
Access 2002 ADP Project with SQL backend (actually MSDE backend)
I want to be able the contents of a list box to Excel. The RowSource
property of the list box is a "SELECT" statement refering to tables in the
MSDE backend.
I've looked at many ways of doing this (DoCmd.OutputTo;
DoCmd.TransferSpreadsheet; etc.) but I can't get it to work. From what I've
been reading it looks like the best way of doing this would be by creating a
view from my SQL statement and then using "DoCmd.OutputTo
acOutputServerView..."
My problem right now is creating the view from my SQL statement. No matter
what I try, I eventually get error 3251 "Object or provider is not capable
of performing requested operation".
Here is the code I am trying (cut and paste from Microsoft web site so exact
content is different from what I am using)
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Select * From Customers"
cat.Views.Append "AllCustomers", cmd
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set cmd = Nothing
No matter how I play with the above code, I always get error 3251. On
trying to get more information, I found an article
(http://ourworld.compuserve.com/homepages/attac-cg/AtblTip.htm#ADOXCREATE)
which states:
Run-time Error: 3251
"Object or provider is not capable of performing requested operation."
This will occur if you set the Connection for the ADOX catalog object to
be equal to connection string returned by CurrentProject.Connnection.
Access' base connection does not support table creation because Access uses
an intermediate OLEDB provider to access the SQL Server. To successfully
create a table on a SQL Server from within Access, you must open a new
connection to the server and use that connection on the server's catalog....
It's essentially saying that I should not use the CurrentProject.Connection
but rather open a new connection using something like:
conn.ConnectionString = "Provider=SQLOLEDB.1;Security Info=False;" & _
"Data Source=YourServerNameHere;Integrated Security=SSPI;" & _
"Initial Catalog=Northwind"
Now I've tried this and although I can create a table (which I could not
with CurrentProject.Connection) , I still cannot create a view. I still get
error 3251.
So, my question is this:
1. Can I easily export a list box RowSource (or SQL statement) to Excel
without creating a view or stored procedure?
2. If I need to create a view first, how can I do this from an ADP project
with MSDE backend?
Thanks
Access 2002 ADP Project with SQL backend (actually MSDE backend)
I want to be able the contents of a list box to Excel. The RowSource
property of the list box is a "SELECT" statement refering to tables in the
MSDE backend.
I've looked at many ways of doing this (DoCmd.OutputTo;
DoCmd.TransferSpreadsheet; etc.) but I can't get it to work. From what I've
been reading it looks like the best way of doing this would be by creating a
view from my SQL statement and then using "DoCmd.OutputTo
acOutputServerView..."
My problem right now is creating the view from my SQL statement. No matter
what I try, I eventually get error 3251 "Object or provider is not capable
of performing requested operation".
Here is the code I am trying (cut and paste from Microsoft web site so exact
content is different from what I am using)
Dim cmd As New ADODB.Command
Dim cat As New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "Select * From Customers"
cat.Views.Append "AllCustomers", cmd
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set cmd = Nothing
No matter how I play with the above code, I always get error 3251. On
trying to get more information, I found an article
(http://ourworld.compuserve.com/homepages/attac-cg/AtblTip.htm#ADOXCREATE)
which states:
Run-time Error: 3251
"Object or provider is not capable of performing requested operation."
This will occur if you set the Connection for the ADOX catalog object to
be equal to connection string returned by CurrentProject.Connnection.
Access' base connection does not support table creation because Access uses
an intermediate OLEDB provider to access the SQL Server. To successfully
create a table on a SQL Server from within Access, you must open a new
connection to the server and use that connection on the server's catalog....
It's essentially saying that I should not use the CurrentProject.Connection
but rather open a new connection using something like:
conn.ConnectionString = "Provider=SQLOLEDB.1;Security Info=False;" & _
"Data Source=YourServerNameHere;Integrated Security=SSPI;" & _
"Initial Catalog=Northwind"
Now I've tried this and although I can create a table (which I could not
with CurrentProject.Connection) , I still cannot create a view. I still get
error 3251.
So, my question is this:
1. Can I easily export a list box RowSource (or SQL statement) to Excel
without creating a view or stored procedure?
2. If I need to create a view first, how can I do this from an ADP project
with MSDE backend?
Thanks