[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Access with SQL

I have an Access Database with a connection to a 2000 SQL server database with the name of Reef. I can modify all tables except for the DTE table. The DTE table when opened from access can be viewed; this table consists of a date that I must change to run reports. I have checked permissions and they look the same for all tables. Can someone guide to find what is causing this problem?
0
ipcipher
Asked:
ipcipher
  • 6
  • 5
  • 2
1 Solution
 
GRayLCommented:
DTE Table?
0
 
ipcipherAuthor Commented:
Yes the DTE table it consist of an ID, Startdte, and Enddte fields.
0
 
GRayLCommented:
Is it a system table of some kind, or is that its name?  If any of the fields are calculated fields you will not be able to modify them.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
ipcipherAuthor Commented:
That is the name of the table. We were able to change the values of  this table before. I don't know what changed in the table that is preventing us from updating the table.
0
 
adraughnCommented:
Can you change the data in SQL?
0
 
ipcipherAuthor Commented:
Yes. I can go into the enterprise manager and return all rows in the dte table and modify the data from that menu.
0
 
adraughnCommented:
delete the link to your dte table and relink.
0
 
ipcipherAuthor Commented:
Can you guide me through the deletion of the link to dte table and relinking. not very familiar with sql.
0
 
adraughnCommented:
i meant delete the linked table in access. in the database window, under tables, there will be a sql table link for your DTE table (distinguished with a globe icon). select the table link, then hit the delete key. then add a new table, as a link, linking it to sql. if you need help with that part post back.

-a

note: if it is not a linked table, but a pass thru query that is being used as your source, that would be why you cannot make changes. PT queries are read only.

0
 
ipcipherAuthor Commented:
When I delete the dte table in access it is also deleted in the SQL server. The tables are not linked they seem to have a direct connection to the sql database. In Access I can click on file and then connection, it show me  the data link properties, name of the server, and database. It also show that it is set to use the windows nt integrated security.  
0
 
adraughnCommented:
you need to link the table. Follow these steps:

a) From the tables tab in access, click on new - link table
b) Change 'Files of Type' to 'ODBC Databases'
c) Click on 'New' on the Select Data Source tab
d) Scroll down in the listbox and choose SQL Server as your driver
e) Type in a data source name and click Next
f) Click Finish
g) Type in a description for the database and select your server from the drop down or type it in then click Next
h) i would suggest using NT authentication, but it's your choice - make it and click next
i) click on 'change the default database to'. you don't want to store your data in master. you will need to create a database on the server specifically for your database. Once you have, select the database name from the drop down.
j) Click next - click finish - click Test Data Source
k) Click OK and then OK again
l) Click on the .dsn file that you just created and click OK
m) a list of tables will appear that are in that database on the server. select the tables that you would like to link from the list and click OK
n) you will want to open the table in design view and check the primary keys.

you would link a SQL View the same way, except it will prompt you for a primary key. Note that if you do not provide a primary key for a view, it will be read only.

oh, you only have to create the .dsn once. if you want to link more tables/views, you just use the .dsn that you already created.

once you have your tables linked, notice that they come in as dbo_tablename. You will need to rename the tables as tablename, removing the dbo_. Then you can delete your access table (hopefully a link to a table if you have a split db) and your queries will work with the sql table.
0
 
ipcipherAuthor Commented:
It was the primary key. Thanks!
0
 
adraughnCommented:
you're welcome!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now