| Author |
Topic |
|
Pinecricker
Starting Member
8 Posts |
 Posted - 2008-03-09 : 19:13:04
|
| If I create a new table in my database I can not update or delete any records when I'm connected with ODBC. I can access all the other tables in the database just fine with ODBC, it's only when I create a new table or copy and existing table that this happens. Is there some setting I need to change, if so how do I change it?Thanks,TH |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-09 : 19:28:31
|
Double check the user permissions/mapping on the server side. If user/role permissions are object specific, the new table won't be visible via ODBC.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
Pinecricker
Starting Member
8 Posts |
Posted - 2008-03-09 : 19:45:10
|
Not sure how to check for this? Fairly new to SQL Server. Thanks,THquote:
Originally posted by dataguru1971
Double check the user permissions/mapping on the server side. If user/role permissions are object specific, the new table won't be visible via ODBC.
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-09 : 19:55:53
|
You would need SQL Server Enterprise Manager or SQL Server 2005 Management Studio...If you have one of those, you can connect to the server and look for "logins" under "Security"...you can then check the properties of the login.If not, you will have to contact the Server's DBA to modify/check your permissions.Alternatively, how are you creating the tables? Are you sure they are being created in the default ODBC Connection database? You may be inadvertantly creating them in another database and when connecting via ODBC, you need to change databases to see them?
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Pinecricker
Starting Member
8 Posts |
Posted - 2008-03-09 : 21:38:26
|
The user I'm loggging in as has full sysadmin priveledges. I've tried to change the permissions at the table and database level, and I guess I'm doing something wrong because nothing changes. I can see the table logged in from ODBC, but I can not update or delete any records.quote:
Originally posted by dataguru1971
You would need SQL Server Enterprise Manager or SQL Server 2005 Management Studio...If you have one of those, you can connect to the server and look for "logins" under "Security"...you can then check the properties of the login.If not, you will have to contact the Server's DBA to modify/check your permissions.Alternatively, how are you creating the tables? Are you sure they are being created in the default ODBC Connection database? You may be inadvertantly creating them in another database and when connecting via ODBC, you need to change databases to see them?
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-09 : 21:47:49
|
I thought you couldn't see the table before? maybe I misread something.What is your client tool? Are you using ODBC to connect from Access or another application?What is the context that you are unable to delete or make updates to the server tables? Is this ONLY for when you create a new table?EDIT: If you are using a connection string to connect, as in Access VBA or C# or something...please post that. If you use a connection and OLEDB or ADODB to select the records, the table may be locked depending on how you are connected...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-09 : 21:55:18
|
| Whicn sql login do you use in odbc dns? |
|
|
|
Pinecricker
Starting Member
8 Posts |
Posted - 2008-03-09 : 22:16:53
|
I'm using MS Access with SQL Native Client ODBC Driver, and I'm using the sysadmin login and password in the connection string. I can connect to the server tables and can update and delete records in all the existing tables. I just can't update or delete records in a new table I created. I can see the new table, just can't change add or delete any records in it when I connect with Access and ODBC (if I open the table in Management Studio I can add and modify records, etc.) I've tried creating the table by copying an existing one (insert into) and by creating it from scratch, both have the same problem. I also tried to change the permission for the table by selecting the table, right clicking, going to properties, permission and granting all permission for my user (even though it is alrady sysadin) and nothing changes. Not sure where else I would need to change permissions? I've tried creating a new file dsn and tried several logins to no avail. TIAquote:
Originally posted by dataguru1971
I thought you couldn't see the table before? maybe I misread something.What is your client tool? Are you using ODBC to connect from Access or another application?What is the context that you are unable to delete or make updates to the server tables? Is this ONLY for when you create a new table?EDIT: If you are using a connection string to connect, as in Access VBA or C# or something...please post that. If you use a connection and OLEDB or ADODB to select the records, the table may be locked depending on how you are connected...
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-09 : 22:35:42
|
Hmmm.Access has some quirks, but it is odd that you can create the table, update previously created ones, but not update the one recently added. Again, you doing this via the Access screens, make table queries and such, or are you doing it via VBA code?If i were experiencing this, I would probably create the table in Access and leave everything as is..stay logged in and don't close access. Then I would go to the SQL EM or SMSS and see if the table is locked. It sounds like the table is locked.You also say you can't update the table, but you have said if:1.You receive a "non updateable query" error--this means when linking to the new table via ODBC you didn't identify the linked tables primary key. It must have one for Access to be able to do an update query.2.An ODBC error3.or another error of some kind.--if so, what is the actual error messageIf there is VBA code, snap a sample of the sequence and connection string into the post.When you first connect to the server via ODBC it creates a connection, most times with the persist security info=TRUE such that you don't have to enter the login/password every time..it may be placing a lock on the table after you create it.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Pinecricker
Starting Member
8 Posts |
Posted - 2008-03-09 : 22:57:15
|
BINGO!!!!When I link the table and select the fields that are unique identifiers I can update, delete and add records!!!! I thought I had a primary key defined, but apparently access didn't like it.Thanks a million for your help. Very much appreciated!!!quote:
Originally posted by dataguru1971
Hmmm.Access has some quirks, but it is odd that you can create the table, update previously created ones, but not update the one recently added. Again, you doing this via the Access screens, make table queries and such, or are you doing it via VBA code?If i were experiencing this, I would probably create the table in Access and leave everything as is..stay logged in and don't close access. Then I would go to the SQL EM or SMSS and see if the table is locked. It sounds like the table is locked.You also say you can't update the table, but you have said if:1.You receive a "non updateable query" error--this means when linking to the new table via ODBC you didn't identify the linked tables primary key. It must have one for Access to be able to do an update query.2.An ODBC error3.or another error of some kind.--if so, what is the actual error messageIf there is VBA code, snap a sample of the sequence and connection string into the post.When you first connect to the server via ODBC it creates a connection, most times with the persist security info=TRUE such that you don't have to enter the login/password every time..it may be placing a lock on the table after you create it.
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-09 : 23:15:56
|
I guess I should have asked those questions right off the bat. Not being able to update tables, and having a "not updateable query" in Access are pretty different from a diagnostic standpoint.Glad you got it worked out
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2008-03-09 : 23:21:22
|
quote:
Originally posted by dataguru1971
I guess I should have asked those questions right off the bat. Not being able to update tables, and having a "not updateable query" in Access are pretty different from a diagnostic standpoint.Glad you got it worked out, wish I had read the first post the right way. My first few questions were around the idea that you couldn't even SEE the table you created.
Poor planning on your part does not constitute an emergency on my part.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
Can not update or delete records from ODBC...