I have a fe that has 5 tables linked to a backend db on the server.I want to add a new table to the fe that is linked to a second db on the server? What I have tried.I have created the tbl_called in the fe.when I right click on it and go to Linked Table Manager I do not see it on the list. The only tables in the list are the 5 I linked before. Why is tbl_called not showing up??
View RepliesIn Access2010 I have created a linked table to a SQLServerExpress database.I have a user in SQLserverExpress with only reading rights.This linked table is used in a form which only shows the information. When opening the form, it prompts for the SQLserverExpress username and password.How can I configure the linked table so that it logs in automatically with the SQL user I prefer so that the user does not need to enter a user / password?
View 3 Replies View RelatedI have a table that is linked to a survey and pulls data from the limesurvey backend.
The user would like to add a local field, so they can add entries to each row of data.
Is that possible ?
Hi,
I created a database in Microsoft Access and then upsized it to SQL Server afterwards.
I now need to add a new column to a table in the database. So I added the new column in SQL Server using ALTER TABLE command. This column is a foreign key which has an associated parent key in another table which contains a description of the column.
e.g.
Main_Table
Case_ID Foreign_Key_Field_ID
1234 78
New_Foreign_Key_Table
Foreign_Key_Field_ID Foreign_Key_Field_Desc
78 Describes the ID 78
I also setup a CONSTRAINTREFERENCE between the fields in these 2 tables.
I then relinked my tables using the Linked Table Manager to pick up the change to the table I altered (Main_Table). I also linked to the new table I created (New_Foreign_Key_Table).
My problem:
In Access if I create a new table I can select the lookup value for a field in the Table Design view Lookup tab. However, I altered my table in SQL Server and then linked to it again to pick up the change. Now when I view the linked table in Access it does not have any lookup value AND it's not possible to change this for a linked table.
Is there some piece of SQL I can use in SQL Server to change the lookup value for the field in my table OR have I approached this the wrong way. Should I have modified the local table in Access and upsized it again?
Otherwise if I select all values from my Main_Table in a form it displays the Foreign_Key_Field_ID instead of the Foreign_Key_Field_Desc because the lookup value has not been set to Foreign_Key_Field_Desc
This posting is a bit long - I hope it makes sense.
Any ideas or suggestions would be greatly appreciated.
Many Thanks
I am in need of some assistance regarding SQL Server ODBC links and security. This is for a project at work...
1) I have an MDB that has linked tables through ODBC to a SQL Server 2000 database. This is how I chose to write this multi-user application (instead of the ADP method).
2) My IT department created a user ID that the application will use to connect to the database. They chose this method instead of having to create many user accounts with varying permissions. I have programmed permissions within the application.
3) After linking the tables through ODBC, it defaults to my Windows User ID for the connection. Because of this, I have used a connection string with the application's User ID/Password and perform a RefreshLink on all linked table definitions on startup to ensure valid connection.
This is successful, but my IT department is seeing that another user is attempting access to the SQL Server database--the username in the LDB/MDW file! This is what I don't understand. If I open the database without going through workgroup security, the word "Admin" is passed and the authentication fails. If my users would login through the MDW file, their user IDs would be passed and again, authentication will fail as they are not valid users (only the application ID would be valid).
What is really going on here? :confused: How can I prevent the "Microsoft Access User ID" from being passed through the ODBC link?
Here is a sample of the connection string I'm using:
Driver={SQL Server};Server=ServerName;PersistSecurityInfo=Yes; AnsiNPW=No;Database=DBName;Uid=Username;Pwd=Passwo rd;
Hi,
My access database's tables are currently linked to an excel document. My question is, how do I move my database to the network? Currently when I move the the database to the network no one can open my tables because the link goes to my documents on my computer which they can't access on the network.
So how do I move the database and maintain the link between the database and my tables?
Thanks in advance,
Ryan Smith
Hi you geniuses!
I just had to upsize my loved and complex Access database to SQL server 2000 and had to link all tables via ODBC into my client Access database.
I used to use pessimistic locking on all tables when the tables were in an Access database before - worked fine ...
but now my client Access database uses "No Locks" on these tables because they are controlled by SQL server 2000.
Does some genius know how to change this bad behaviour? :confused:
We are sharing an Access 2003 database amongst two departments that contains twelve (12) tables that are SQL Server linked tables. Nine (9) of the tables work just fine for people in both departments. Three (3) of the tables cannot be opened until the user refreshes the link(s) using their ODBC DSN.
We do not understand why nine of the tables work without issue but three will not.
Has anyone ever run into this issue before? Does anyone have any ideas on how to resolve this issue?
Thanks
Hi,
I used ODBC to link the tables from SQL Server to MS Access.
When I link the table, it popup the message:
Select Unique Record Identifier
To ensure the data integrity and to update records, you must choose a field or fields that uniquely each record. Select up to ten fields.
It force me to set up at least 1 field for primary key in a table.
How can I skip it? I do NOT want to setup primary key of the fields in a table.
Please let me know, thanks.
Hi all,
wonder if anyone can assist me with this the SQL Server
security/write (?) issue. The SQL Server Admin has created a table for our Dept. to use and via SQL Server I can get access to it using the username and password she supplied to me. I can also link to this table in Access 2002 via ODBC which is the way I am using to write to SQL server tables. I have developed an Access application to allow one of our staff to be able to write data to the SQL Server table at a click of a button, however when I open the table the 'add new record' button is greyed out, I can not add any data to the table.
I explained this problem to the Admin as well as the way in which we access the table and she is certain that the problem lies in Access but i'm unsure how that can be the case.
Has anyone experienced this problem before ? Is there a fix and if so how is that implemented ?
Should I also post this in the SQL server forum ?
Thanks in advance,
Mitch....
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I am rebuilding an application for a client and I have an Access table that I am using as a temporary table. Once the user is done entering information into the temporary table through a form, the user presses an update button that appends the records using an Append Query in Access to an SQL Server Table.
The following error message occurs:
"ODBC -- insert on a linked table 'linked tblname' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Explicit value must be specified for identity column in table 'linked tblname' when IDENTITY INSERT is set to ON. (#545)
I am using a form, subform combination to record a bill with many details. The bill summary is posted into a tbl_TransactionsMain table in SQL Server using the ADO AddNew method. The PK for the tbl_TransactionsMain is then entered into the temporary table in Access. When the temporary table records are appended into tbl_TransactionDetail the error message occurs.
What is also interesting is while typing out this post I thought to test the error by manually trying to run the query. The query worked like a charm! :confused: When the orginal error occurred off of the form I tried to run the query manually and it failed. I am guessing that this might have something to do with the ODBC timeout.
I think SQL Server/ODBC connection is not liking how I have a set of records in an Access table with foreign key numbers assigned when I am attempting to append the records. I am new to SQL Server and any ideas are most appreciated! :)
I have a ms access application in Front End and linked tables to sql server 2008 r2 in back end.. every time I open my application the linked tables ask to me for the user and password of the sql server user of the odbc conection. I want to connect to my sql database without login every time the user open the application, I know that can be possible if I start session from vba code...
View 2 Replies View RelatedI have a database with a front end and back end (tables on the server) that was recently converted from Access 2003 to Access 2010.
For some reason the back end keeps hanging and I have to constantly compact and repair and there has been major data loss on a regular basis.
Hi folks,
I am really struggling w/ the following & would greatly appreciate advice!
I want to add several Fields to a Linked Table in Access. The Access wouldn't let me do it because it is a Linked Table. What should I do?
There is also a nicely designed Form that goes w/ that Linked Table. So I figured that once I add the Fields in the Table then I can add them to the Form too. - But can't even add the fields yet....
HUGE THANKS in advance!
Greetings all.
I'm posting this question here as it pertains to my form but it could go in tables section as well.
I developed a prototype app in Access and have just finished upscaling it
to Sybase 12.5. When I open up the associated form to add records, I am unable to do so. The built in button that allows you to do so is greyed out.
When I go into the table directly, again, no ability to add recs.
I've never come across this so if anyone has any experience with this, any help would be most welcome.
TIA,
Tim
I need to update data in a bunch of tables of a sql server database. The database has 300 tables which I have linked via odbc. I'm hoping there is a simple way, using vba, to loop through my linked sql server tables and determine which of them are views as opposed to tables.
View 1 Replies View RelatedCan someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? I am using Access 2002. I also wonder if someone can tell me how to modify a field in a linked table? I have tried altering and saving the changes, but it won't save them because it's a linked table. Thank you in advance to anybody who can help me out here!
View 2 Replies View RelatedI have several tables that are linked that I need to be able to add to. This is an example of the structure:
Patient Info
ID
FK_Ward - ID of Ward table
FK_Room - ID of Room table
FK_Cond - ID of Condition table
Ward
ID
WardID
Room
ID
RoomNumber
Condition
ID
Description
Unfortunately, its not the exact one as I can't post that here! What I am trying to do is to add a new patient record using information from the other tables as guides - e.g. the user selects the name of the Ward but the ID in the Ward table is entered, the User selects the number of the room but again the ID in the Room table is entered, and for the Condition same thing. So I would like to have dropdowns (or ComboBoxes) for each of these and based on the user's selections add a new record to PatientInfo using the ID values as links.
Is it possible to add new records to a linked table ? i tried it out but new records is not possible, is there a workaround for this.
View 8 Replies View Relatedlooking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
I have an Access 2010 front end with linked tables via ODBC to a SQL Server back end. Is it possible to add, delete, and otherwise alter the tables on the SQL server from the Access front end? I've tried the following but received an error "cannot execute data definition statements on linked data sources"
DoCmd.RunSQL "alter table [dbo_tblAccountsMvOld] add column [cnt] byte;"
Is there code that will let me do this?
Is it same thing about speed and other issues to have ADP application that works with SQLserver and to have a MDB (or MDE) application that has no querys but works with sql views instead?
Thanks
Hi.
I'm about to move to a back end / front end system, from a single database file. Before I do so, I want to make sure I have all bases covered so to speak.
My back end file will reside on a server. I am aware that path names are sometimes changed by network administrators without warning.
From tests, I notice that, if the path to the back end can't be found, one cannot open the front end database file at all (Access 2003).
My question is: if my back end file gets moved, or the path changed, how can I get into my front end file to update the linked tables.
Many thanks in advance.
Mat.
I have built a database and within it my queries I use for reporting make tables in a separate database. This allows me to work in my data base and update info as needed. My problem is that my make table queries wont run if anyone has one of the report pivot tables open. I have added macros to all the Excel files that pull from the reporting DB but I still have several times when I cannot update because someone is using the pivot table, Is there a way to allow me to update the report DB's while the pivot tables are open? I have tried using manual refresh methods and disabling auto update on open but the result is the same.
View 2 Replies View RelatedI recall reading somewhere (fairly recently) about a parameter or property in Access which is "On" by default and allows a form/query to identify when backend table field names or query arguments are changed and automatically looks for and "fixes" the renamed link. By leaving this switched ON, the database is slower than it could be. The db speed performance can be can be improved significantly by switching this 'feature' off. (i.e. Name of the property)
View 3 Replies View Related