|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ado.net create a tableI 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, freetorn2***@yahoo.ca (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, freetorn2***@yahoo.ca (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 Show quote Paul Clement <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:<bidl4114e1nqqmp4p0ad06rc84as2l046s@4ax.com>... > On 30 Mar 2005 04:51:31 -0800, freetorn2***@yahoo.ca (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) On 31 Mar 2005 04:34:13 -0800, freetorn2***@yahoo.ca (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 Show quote Paul Clement <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:<tm5o41h1tik71c2vvn2s12n75battp90ib@4ax.com>... > On 31 Mar 2005 04:34:13 -0800, freetorn2***@yahoo.ca (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) 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=" Show quote "viki" <freetorn2***@yahoo.ca> wrote in message news:deb17ac9.0503311135.5191df49@posting.google.com... > 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 <UseAdddressAtEndofMess***@swspectrum.com> wrote in message > news:<tm5o41h1tik71c2vvn2s12n75battp90ib@4ax.com>... >> On 31 Mar 2005 04:34:13 -0800, freetorn2***@yahoo.ca (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) 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 Show quote "Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message news:<uUCkKcjNFHA.164@TK2MSFTNGP12.phx.gbl>... > 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" <freetorn2***@yahoo.ca> wrote in message > news:deb17ac9.0503311135.5191df49@posting.google.com... > > 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 <UseAdddressAtEndofMess***@swspectrum.com> wrote in message > > news:<tm5o41h1tik71c2vvn2s12n75battp90ib@4ax.com>... > >> On 31 Mar 2005 04:34:13 -0800, freetorn2***@yahoo.ca (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) 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 On 31 Mar 2005 11:35:28 -0800, freetorn2***@yahoo.ca (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 Show quote Paul Clement <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:<3ioq41dtp30cl8n68mrosknvp3marm1m0g@4ax.com>... > On 31 Mar 2005 11:35:28 -0800, freetorn2***@yahoo.ca (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) 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 Show quote "Cor Ligthert" <notmyfirstn***@planet.nl> wrote in message news:<unAlGs0NFHA.4092@tk2msftngp13.phx.gbl>... > Viki, > > "Everyone" does not help, it has to be the aspnet user, I give it than as > well to the IIS user. > > Cor On Sat, 2 Apr 2005 08:12:43 +0200, "Cor Ligthert" <notmyfirstn***@planet.nl> wrote: ¤ 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) Paul,
How who know he did that? Cor > ¤ Viki, How you know that Viki did that.> ¤ > ¤ "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. > Cor On Mon, 4 Apr 2005 14:38:55 +0200, "Cor Ligthert" <notmyfirstn***@planet.nl> wrote: ¤ Paul,¤ ¤ How who know he did that? Because viki said so: >> I have not imposed any security to my access 2002 database. All i knowis 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 On Tue, 5 Apr 2005 08:36:22 +0200, "Cor Ligthert" <notmyfirstn***@planet.nl> wrote: ¤ 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) On 1 Apr 2005 17:40:21 -0800, freetorn2***@yahoo.ca (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) Hi Paul,
I am still struggling to get the solution. The exact code is very simple Dim db_conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\users\accessdb\QA\Quality assurance database.mdb") Dim db_cmd As OleDbCommand Dim sqlstr As String db_conn.Open() sqlstr = "SELECT * INTO qa_temp FROM qa WHERE 1=0;" db_cmd = New OleDbCommand(sqlstr, db_conn) db_cmd.ExecuteNonQuery() db_conn.Close() and the same error " COuld not create; no modify design permission for table or query 'qa_temp'" The database is not in the virtual directory, it is in the separate folder but in the same server. I have given full access to everyone, aspnet , iss accounts to the folder as well as the database. One thing that annoys me is that i was able to update or delete or even drop particular table in that database. But i was not able to create a table in the database. is there anything i have to include in the connection string which would enable me to add a table to the database. I would really appreciate any help paul.. Thanks, viki Show quote Paul Clement <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:<k7c251h8e57r1tig9fgjsvjqdfu8n73hlb@4ax.com>... > On 1 Apr 2005 17:40:21 -0800, freetorn2***@yahoo.ca (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) On 5 Apr 2005 06:39:17 -0700, freetorn2***@yahoo.ca (viki) wrote:
¤ Hi Paul, ¤ I am still struggling to get the solution. The exact code is very ¤ simple ¤ Dim db_conn As New ¤ OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data ¤ Source=d:\users\accessdb\QA\Quality assurance database.mdb") ¤ Dim db_cmd As OleDbCommand ¤ Dim sqlstr As String ¤ db_conn.Open() ¤ sqlstr = "SELECT * INTO qa_temp FROM qa WHERE 1=0;" ¤ db_cmd = New OleDbCommand(sqlstr, db_conn) ¤ db_cmd.ExecuteNonQuery() ¤ db_conn.Close() ¤ ¤ and the same error " COuld not create; no modify design permission for ¤ table or query 'qa_temp'" ¤ ¤ The database is not in the virtual directory, it is in the separate ¤ folder but in the same server. I have given full access to everyone, ¤ aspnet , iss accounts to the folder as well as the database. ¤ ¤ One thing that annoys me is that i was able to update or delete or ¤ even drop particular table in that database. But i was not able to ¤ create a table in the database. is there anything i have to include in ¤ the connection string which would enable me to add a table to the ¤ database. ¤ ¤ I would really appreciate any help paul.. I'm beginning to wonder whether your database might be corrupt. Have you tried creating and importing into a new Access database? Paul ~~~~ Microsoft MVP (Visual Basic) hi paul,
I would give a try tommorrow and see how it works.. Thanks, Viki Show quote Paul Clement <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:<emc551d5q9mmlpesc3kve6u3275gvk2lp7@4ax.com>... > On 5 Apr 2005 06:39:17 -0700, freetorn2***@yahoo.ca (viki) wrote: > > ¤ Hi Paul, > ¤ I am still struggling to get the solution. The exact code is very > ¤ simple > ¤ Dim db_conn As New > ¤ OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > ¤ Source=d:\users\accessdb\QA\Quality assurance database.mdb") > ¤ Dim db_cmd As OleDbCommand > ¤ Dim sqlstr As String > ¤ db_conn.Open() > ¤ sqlstr = "SELECT * INTO qa_temp FROM qa WHERE 1=0;" > ¤ db_cmd = New OleDbCommand(sqlstr, db_conn) > ¤ db_cmd.ExecuteNonQuery() > ¤ db_conn.Close() > ¤ > ¤ and the same error " COuld not create; no modify design permission for > ¤ table or query 'qa_temp'" > ¤ > ¤ The database is not in the virtual directory, it is in the separate > ¤ folder but in the same server. I have given full access to everyone, > ¤ aspnet , iss accounts to the folder as well as the database. > ¤ > ¤ One thing that annoys me is that i was able to update or delete or > ¤ even drop particular table in that database. But i was not able to > ¤ create a table in the database. is there anything i have to include in > ¤ the connection string which would enable me to add a table to the > ¤ database. > ¤ > ¤ I would really appreciate any help paul.. > > I'm beginning to wonder whether your database might be corrupt. Have you tried creating and > importing into a new Access database? > > > Paul > ~~~~ > Microsoft MVP (Visual Basic) Yeah!! i got it working. Thanks Paul , u are right i imported
everything to a new access database and it worked like a charm. Sorry for wasting your time for simple things. But i did not realise that it is corrupted , since it has been working without any problem. May be if i have tested in some other database , i might have known this.. Any how thanks for everyone for sharing the information.. Thanks, Viki Show quote freetorn2***@yahoo.ca (viki) wrote in message news:<deb17ac9.0504051721.564b0c8c@posting.google.com>... > hi paul, > I would give a try tommorrow and see how it works.. > > Thanks, > Viki > Paul Clement <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:<emc551d5q9mmlpesc3kve6u3275gvk2lp7@4ax.com>... > > On 5 Apr 2005 06:39:17 -0700, freetorn2***@yahoo.ca (viki) wrote: > > > > ¤ Hi Paul, > > ¤ I am still struggling to get the solution. The exact code is very > > ¤ simple > > ¤ Dim db_conn As New > > ¤ OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > > ¤ Source=d:\users\accessdb\QA\Quality assurance database.mdb") > > ¤ Dim db_cmd As OleDbCommand > > ¤ Dim sqlstr As String > > ¤ db_conn.Open() > > ¤ sqlstr = "SELECT * INTO qa_temp FROM qa WHERE 1=0;" > > ¤ db_cmd = New OleDbCommand(sqlstr, db_conn) > > ¤ db_cmd.ExecuteNonQuery() > > ¤ db_conn.Close() > > ¤ > > ¤ and the same error " COuld not create; no modify design permission for > > ¤ table or query 'qa_temp'" > > ¤ > > ¤ The database is not in the virtual directory, it is in the separate > > ¤ folder but in the same server. I have given full access to everyone, > > ¤ aspnet , iss accounts to the folder as well as the database. > > ¤ > > ¤ One thing that annoys me is that i was able to update or delete or > > ¤ even drop particular table in that database. But i was not able to > > ¤ create a table in the database. is there anything i have to include in > > ¤ the connection string which would enable me to add a table to the > > ¤ database. > > ¤ > > ¤ I would really appreciate any help paul.. > > > > I'm beginning to wonder whether your database might be corrupt. Have you tried creating and > > importing into a new Access database? > > > > > > Paul > > ~~~~ > > Microsoft MVP (Visual Basic)  |
|||||||||||||||||||||||
Other interesting topics