ado.net create a table

  • Thread starter Thread starter viki
  • Start date Start date
V

viki

Hi ,
I want to copy a dbf file to the the access database.I thought of two
solutions
1. Copy the dbf to the access db by the query
SELECT * into dest_table from [source.dbf] in 'source folder'
this does not work out, it gave me the error that u have no
permission
2.create a table with the same structure of dbf file and then insert
the rows using oledbcommandbuilder update commands
i tried to create the table by the following query
create table temp (test1 int,test2 varchar(40))
it gave a error message "you donot have the necessary permission to
use the 'temp' object."

I believe there is something to do with the permission or something
other than that could some one tell me what is the solution..

Thanks in advance,
Viki
 
On 29 Mar 2005 11:05:33 -0800, (e-mail address removed) (viki) wrote:

¤ Hi ,
¤ I want to copy a dbf file to the the access database.I thought of two
¤ solutions
¤ 1. Copy the dbf to the access db by the query
¤ SELECT * into dest_table from [source.dbf] in 'source folder'
¤ this does not work out, it gave me the error that u have no
¤ permission
¤ 2.create a table with the same structure of dbf file and then insert
¤ the rows using oledbcommandbuilder update commands
¤ i tried to create the table by the following query
¤ create table temp (test1 int,test2 varchar(40))
¤ it gave a error message "you donot have the necessary permission to
¤ use the 'temp' object."
¤
¤ I believe there is something to do with the permission or something
¤ other than that could some one tell me what is the solution..

You may want to post your code for #1 so we can see exactly what you are doing.

Also, is this a web application or desktop application?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
HI,
Thanks for your response, THe code for #1 is
Dim db_conn As New OleDbConnection(ConfigurationSettings.AppSettings("connectionstring"))
Dim db_cmd As OleDbCommand
Dim sqlstr As String
db_conn.Open()
sqlstr = "SELECT * into test from [test_dest.dbf] in
'd:\users\accessdb\testfolder'"
db_cmd = New OleDbCommand(sqlstr, db_conn)
db_cmd.ExecuteNonQuery()
db_conn.Close()

This is web application in vb.net.The error i have got is

"The Microsoft Jet database engine cannot open the file
'd:\users\accessdb\QA'. It is already opened exclusively by another
user, or you need permission to view its data."

Do u have any idea how to rectify it or where i am wrong.

Thanks,
Viki
 
On 30 Mar 2005 04:51:31 -0800, (e-mail address removed) (viki) wrote:

¤ HI,
¤ Thanks for your response, THe code for #1 is
¤ Dim db_conn As New OleDbConnection(ConfigurationSettings.AppSettings("connectionstring"))
¤ Dim db_cmd As OleDbCommand
¤ Dim sqlstr As String
¤ db_conn.Open()
¤ sqlstr = "SELECT * into test from [test_dest.dbf] in
¤ 'd:\users\accessdb\testfolder'"
¤ db_cmd = New OleDbCommand(sqlstr, db_conn)
¤ db_cmd.ExecuteNonQuery()
¤ db_conn.Close()
¤
¤ This is web application in vb.net.The error i have got is
¤
¤ "The Microsoft Jet database engine cannot open the file
¤ 'd:\users\accessdb\QA'. It is already opened exclusively by another
¤ user, or you need permission to view its data."
¤
¤ Do u have any idea how to rectify it or where i am wrong.

Yes, it's a permissions issue:

PRB: Cannot connect to Access database from ASP.NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;316675


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Paul,
Yesterday , again i changed the query to
sqlstr = "SELECT * into qa_test from [qa.dbf] in
'd:\users\accessdb\QA' 'dbase IV;'"
since the connection is to the access mdb file and it has to retrieve
from the dbase iv file. I got the error as "Could not Create; no
Modify design permission for table or query 'qa_Test'" , which is the
same error for case 2#.

I looked into the link u gave me, which says impersonation would solve
the problem. Yes this application is impersonated by including
identity impersonate= true in the configruation file. I was able to
select, update but i could not create a new table. Could u please
shed the light on this..

Thanks,
Viki
 
On 31 Mar 2005 04:34:13 -0800, (e-mail address removed) (viki) wrote:

¤ Hi Paul,
¤ Yesterday , again i changed the query to
¤ sqlstr = "SELECT * into qa_test from [qa.dbf] in
¤ 'd:\users\accessdb\QA' 'dbase IV;'"
¤ since the connection is to the access mdb file and it has to retrieve
¤ from the dbase iv file. I got the error as "Could not Create; no
¤ Modify design permission for table or query 'qa_Test'" , which is the
¤ same error for case 2#.
¤
¤ I looked into the link u gave me, which says impersonation would solve
¤ the problem. Yes this application is impersonated by including
¤ identity impersonate= true in the configruation file. I was able to
¤ select, update but i could not create a new table. Could u please
¤ shed the light on this..

Are you using any Access database security? It seems rather odd that you can update the database but
not create a new table.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul,
I have not imposed any security to my access 2002 database. All i know
is everyone was given full access to the database and in the iis it's
integrated windows authentication..
Anything else can u think of..

Thanks,
Viki
 
The database design of an Access/Jet database can not be modified unless it
has been opened for exclusive use.

Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="


viki said:
Paul,
I have not imposed any security to my access 2002 database. All i know
is everyone was given full access to the database and in the iis it's
integrated windows authentication..
Anything else can u think of..

Thanks,
Viki

Paul Clement said:
On 31 Mar 2005 04:34:13 -0800, (e-mail address removed) (viki) wrote:

¤ Hi Paul,
¤ Yesterday , again i changed the query to
¤ sqlstr = "SELECT * into qa_test from [qa.dbf] in
¤ 'd:\users\accessdb\QA' 'dbase IV;'"
¤ since the connection is to the access mdb file and it has to retrieve
¤ from the dbase iv file. I got the error as "Could not Create; no
¤ Modify design permission for table or query 'qa_Test'" , which is the
¤ same error for case 2#.
¤
¤ I looked into the link u gave me, which says impersonation would solve
¤ the problem. Yes this application is impersonated by including
¤ identity impersonate= true in the configruation file. I was able to
¤ select, update but i could not create a new table. Could u please
¤ shed the light on this..

Are you using any Access database security? It seems rather odd that you
can update the database but
not create a new table.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
 
Viki,

Look using explorer to the file permissions and see if the aspnet user has
full persmission on that access database.

Because that he has not, is mostly causing this error.

Cor
 
I use oledb connection string as
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\users\accessdb\QA\Quality assurance database.mdb;User
ID=;Password="

Could u please let me know how could i add the condition for exclusive
use. I have added the Exclusive=1 as
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\users\accessdb\QA\Quality assurance
database.mdb;Exclusive=1;User ID=;Password=".
It gave me the error , "could not find installable ISAM"

Please Help..

Viki
Jim Hughes said:
The database design of an Access/Jet database can not be modified unless it
has been opened for exclusive use.

Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd="


viki said:
Paul,
I have not imposed any security to my access 2002 database. All i know
is everyone was given full access to the database and in the iis it's
integrated windows authentication..
Anything else can u think of..

Thanks,
Viki

Paul Clement said:
On 31 Mar 2005 04:34:13 -0800, (e-mail address removed) (viki) wrote:

¤ Hi Paul,
¤ Yesterday , again i changed the query to
¤ sqlstr = "SELECT * into qa_test from [qa.dbf] in
¤ 'd:\users\accessdb\QA' 'dbase IV;'"
¤ since the connection is to the access mdb file and it has to retrieve
¤ from the dbase iv file. I got the error as "Could not Create; no
¤ Modify design permission for table or query 'qa_Test'" , which is the
¤ same error for case 2#.
¤
¤ I looked into the link u gave me, which says impersonation would solve
¤ the problem. Yes this application is impersonated by including
¤ identity impersonate= true in the configruation file. I was able to
¤ select, update but i could not create a new table. Could u please
¤ shed the light on this..

Are you using any Access database security? It seems rather odd that you
can update the database but
not create a new table.


Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...
Click to expand...
Click to expand...
 
On 31 Mar 2005 11:35:28 -0800, (e-mail address removed) (viki) wrote:

¤ Paul,
¤ I have not imposed any security to my access 2002 database. All i know
¤ is everyone was given full access to the database and in the iis it's
¤ integrated windows authentication..
¤ Anything else can u think of..

Full access to the folder where the database is located or just the database file?

Also, is the web application set up for impersonation?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Paul & Cor,
Full access is given for everyone to the folder and to the database.
Also web application is set to impersonate=true in the web
configuration folder.

Any ideas please..

Thanks,
Viki
 
Viki,

"Everyone" does not help, it has to be the aspnet user, I give it than as
well to the IIS user.

Cor
 
HI Cor,
Ok already i have given permission to asp.net machine account as well
as iis user account. Also i was able to update a record in a
particular table, i was able to drop that table and also create a new
row in it. But i could not create a new table..

Any ideas...

Thanks,
Viki
 
¤ Viki,
¤
¤ "Everyone" does not help, it has to be the aspnet user, I give it than as
¤ well to the IIS user.

Cor,

This is incorrect. If impersonation is enable (under IIS integrated Windows security) then the
identity under which the thread executes is the authenticated user, not the default ASPNET account.

See the following for more info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetap05.asp


Paul
~~~~
Microsoft MVP (Visual Basic)
 
On 1 Apr 2005 17:40:21 -0800, (e-mail address removed) (viki) wrote:

¤ Hi Paul & Cor,
¤ Full access is given for everyone to the folder and to the database.
¤ Also web application is set to impersonate=true in the web
¤ configuration folder.
¤

Try removing the User ID and Password arguments from your Access connection string.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul,

How who know he did that?

Cor
¤ Viki,
¤
¤ "Everyone" does not help, it has to be the aspnet user, I give it than
as
¤ well to the IIS user.

Cor,

This is incorrect. If impersonation is enable (under IIS integrated
Windows security) then the
identity under which the thread executes is the authenticated user, not
the default ASPNET account.
Click to expand...
How you know that Viki did that.

Cor
 
¤ Paul,
¤
¤ How who know he did that?

Because viki said so:
I have not imposed any security to my access 2002 database. All i know
is everyone was given full access to the database and in the iis it's
integrated windows authentication..
<<
Full access is given for everyone to the folder and to the database.
Also web application is set to impersonate=true in the web
configuration folder.
<<
Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul,

I thought this over this over, and forgot it, so good you respond. This is
in my idea when people is using the access database which is placed in the
same virtual directory. I have seen that most people set that outside that.

Cor
 
¤ Paul,
¤
¤ I thought this over this over, and forgot it, so good you respond. This is
¤ in my idea when people is using the access database which is placed in the
¤ same virtual directory. I have seen that most people set that outside that.
¤
¤ Cor
¤

Cor,

Well, setting security properly can be a challenge. You just need to understand how IIS and ASP.NET
authentication works under the different security scenarios.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top