Hi there,
I just upsized my access database which has several tables and query linked to one of the tables residing on a seperate access database. When I do add in the diagram section, I see only table, then my question how would I be able to do the same thing under SQL Server 2005? thank you
I'm having problems seeing the correct data type when linking my Access tables to MS SQL Server 2005. My varchar(max) fields are showing as text(255). I'm using the SQL Native Client.Has anyone else ssen this issue?
View 3 Replies View RelatedAfter Upsizing a table to sql I linked to that table using access db
Now when i use one of my forms i get a [Invalid Object name "tablename''], not sure why but i am clearly link and the table is in sql!
Can you help!
I am have created a test table in enterprise manager v8 and linked this table to an access database. I am able to append to the table from access but cant delete or update.
Ive set the permission settings to allow the user to update and del so cant work out why this isnt working?
ANyone got any ideas?
Hello,
Could someone please tell me (step by step as I am new!) how to establish a link from reporting services to an access database?
Thnaks,
RC
I created a SQl Server 7 database and used an Access 97 frontend on the same computer. The Access frontend uses linked tables to reach the SQl server. The security setup I have is based on the NT Domain Accounts and whatever group
I have them in (example: Mr. D is in Marketing therefore he is in a NT Domain
Group called "SQL_MARKETING"...the SQL Server recognizes that everyone in this
group has access to whatever role I have designated for it in the database").
I can access the linked tables fine on my computer at my desk or at the server. Another person with the exact same administration rights as me can
get to the server through the network neigborhood or by mapping..and they
can access the Access frontend with no problem from their computer. But
when they try to open a linked table they recieve an error message that
the ODBC connection has failed. We are all running Access 97 service pack
2 and another person and me have the exact same administration..but only
I am able to access the Access linked SQL tables but not him.I tried deleting
the ODBC system dsn on the server and the linked tables and then re-adding them and the ODBC link but to no avail. Does anyone know why this would
be occuring? Thank You in advance.
and the
I tried this syntax in SQL Mngt Studio which i got from SQL menu.
EXEC sp_addlinkedserver
@server = 'Test2',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:MSOfficeonus.mdb'
GO
Linked is created but when i tried to open the table from bonus.mdb using the linked servers-Test2-Catalogs-System Catalogs-default-Tables the tables are not showing and throwing an error of OLE DB provider ....... for linked server "Test2" reported an error. Authentication Failed. Cannot initialize..
We recently upgraded a number of databases from MS Access 2000 to SQL Server 2000. I need to make it so that users can display the data from the SQL Server so I created some link tables in Access for them to view the data without using SQL Server Enterprise Manager. This works fine in tables with only a few thousand rows of data. However some of the tables have 100K+ rows of data, and these are not displaying the data. When someone tries to view the data in one of these tables all they see is #Deleted in every column. Is it possible to link to tables in SQL Server with large numbers of rows and view this info in Access?
Miranda
OK here is my situation: I work in a laboratory with a 10 year old instrument that stores results in an Access 97 DB. The DB is beautifully designed and implemented. The problem is that since the software is so old, once the DB gets above a few MB in size the application starts to fail. So their solution is to Backup (make a copy) and Cleanup (delete data older than X days) once a week. So what I end up with is a bunch of copies of the DB, which fragments the data that I need. In order to get the data results I have been using an Access 2003 DB linked to this one (because I can't create Views in that database). The lab users then use the linked DB directly or I have written some automation code in Excel VBA (ADO) to simplify it for the other users. This allows us to query data immediately as it is produced.
My hope is that I can somehow get all of the data into a SQL Server DB. Now I have imported one of the backup copies of the DB in and the table structure and data imports fine into SQL Server, but I end up with duplicate rows because the different DB backup files have overlapping data, and for some reason there aren't PKeys. Also, I'd prefer to have some way of automating the input. Something like a trigger on the Access table so that any new data is imported into the SQL DB automatically. I doubt this is possible, but that is generally what I am trying to do and I am out of ideas. The ultimate goal is that the SQL DB will always be "updated" and I can use that as my query source and take advantage of the power of SQL Server.
Hopefully that all made sense... if anyone has any ideas I would love to hear them.
Thanks for your time.
Is there a way to link MS Access tables with SQL Server tables so the client app sees them as tables from one database?
I have tried linked server but it appears that it gets stale after a while and needs to be re linked from time to time which is not very practical.
Hi,
I have an existing Access DB with many Tables, Forms, Queries and Reports. I need to put a SQL Server Back end on this database.
Do I need to code a mirror images of these objects or is there some way to export these into SQL Server?
Also, where do I deal with assigning usernames and passwords? Access or SQL Server. I need to have different views for different users based upon their rights (user, admin)
Thanks,
Mark
Hello,
I am using MS Access 2000 as a front end to a SQL Server 2000 db. When I link to the SQL Server db using a DSN, the SQL Server table pk's and indexes are not coming over with the link. The pk's and indexes do exist in SQL Server. I haven't had to link to these tables in over a year, but they used to link just fine.
Do you have a suggestion as to where to look at this problem?
Thanks you - Darryle
Hi, I need to link an Access 97 database to sql server 2000. I can't use Enterprise Manager so I have created a simple asp.net page using sp_addlinkedserver and sp_addlinkedsrvlogin.
The mdb file is on a network share, then I have permissions problems: here is a sample
sp_addlinkedserver 'test', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\SVKNFS080Pshareaccess97db1.mdb', 'efco'
sp_addlinkedsrvlogin 'test', false, 'sa', 'Admin', NULL
I have no problems executing these stored procedures (the linked server is added in sysservers table), but when I try to query the access database
SELECT * FROM test...profile
I get this error
Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message:
The Microsoft Jet database engine cannot open the file '\SVKNFS080Pshareaccess97dboutlet.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
The mdb file is not locked, I think is a permission's problem.
Can anyone help me ?
Thanks in advance
We recently installed SP2 on our SQL Server 7.0 database, and it appears to have changed the way Access databases are linked. Any user other than SA cannot connect to the linked Access tables. We receive the error 7302, which is 'Could not create an instance of OLE DB provider...'. Before the SP2 upgrade, this link was working fine, and non SA personnel (DBOs included) could connect to the linked DB and run queries.
We tried dropping and recreating the Access link, and it still has the same results. We also tried connecting to another Access database, and had the same results. The Access databases we are trying to connect to have no built in security, and the sharing permissions appear to be working because the SA can connect.
Has anyone else run across this error? Is it a problem with SP2?
Hi,
I am using an MDB file to talk to a SQL Server. I would like to use pass-through queries to keep as much of the processing on the server as possible. However, I have been told that the results of such queries will be read-only. How do I keep the processing on the server but generate read-write answers to my queries?
Thanks
Matt
hello
we suppose to link the form in MS Access with Oracle
can anyone tell me how to write the code for these buttons
i tried to write as I learned in VB.NET,
But it seems doesn't work :(
http://www3.speedyshare.com/data/191404757/10803567/78224332/db2.jpg
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
I am trying to add a linked table to my server, it is an access table.
Here is the code i am using but i get an error:
EXEC sp_addlinkedserver
@server = 'AITdb_be2',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access',
@datasrc = '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AITdb_be2',
@useself = 'false',
@rmtuser = 'Admin',
@rmtpassword = ''
GO
-- List the tables on the linked server
EXEC sp_tables_ex 'AITdb_be2'
GO
ERROR:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".
I have a sql server 2005 db.
Is there anyway I can link access to be used as a frontend by my client for viewing the data?
is this possible through linked table manager?
Please advise?
Hello,We have a database in MS SQL 2000 and I'm trying to link to it from MSAccess XP. I've created a user with full access to one specific tablein the MS SQL database, but Access won't write to it.I can open this table in MS Query Analyzer and log-in as the user, andI can do update, delete, and select queries just fine... but when Ilink to the table from MS Access using the same MS SQLusername/password, I can only read the data... no write or delete.Since I can update data fine in MS Query Analyzer, I assumepermissions are setup properly. Anyone know why I can not edit thelinked data from MS Access?Thanks,Alex.
View 1 Replies View RelatedLooks like the Office 2003 MS Access has no support for creating a linked server and implement design changes. While you could link to the SQL 2005 tables, which show up as queries in MS Access 2003, any further use of the linked queries are not supported unless the Access 2003 version came out after the SQL 2005 release. What kind of an update is needed, or fix is needed so that Access projects can be developed?
View 6 Replies View RelatedHi,
I have an app that creates and attaches tables from SQL Server into an Access mdb using an ODBC link from VB6 using DAO 3.51 on Windows 2000. Recently I upgraded to SQL Server Express edition and tested it. It worked fine against the Express Edition. So, I installed SQL Server 2005 Standard Edition on our Server hoping that they operate identically. How wrong one can be! the same code that worked against Express Edition does not work on Standard Edition.
It is strange that the App works for about 5 seconds which attaches about 38 tables and after that the connection drops and I have a hell of a time to get the rest of the tables linked. The error that comes up is:
Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.
The PC that I am using to connect is in a different domain to the Server where the SQL Server database resides. I thought that domains might be the reason. But even joining the PC to the same domain as the server still does not work and behaves the same. So I do not think it is a domain related issue.
I have also looked at the SQL Server and database configurtaions. I cannot see anywhere any specific time out of around 5 seconds. Even the time out through the SQL Server ODBC driver is set to 30 seconds by default as far as I know. So, it looks like there is a time out somewhere but I am absolutely confused as to where this 5 second time out is comming from.
I also thought permissions on the database or tables might be the issue but after looking at the database and table permissions, they all look OK. The user has prettymuch the same access as the SA on the database.
Has anybody came across this problem? If so, I appreciate if someboday can give me a hint as to where I can start.
Thanks,
Hi,
I have an MS-Access database on a shared drive.The Access database has tables linked to a SQL server database.When I try to query the tables in MS-Access database by accessing the share drive,I get the ODBC--call failed error.So I tried creating an ODBC driver for the SQL Server and when I try to link the tables,no tables are displayed.Can anyone help me out with this problem?
-Ruth
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
hi all,
is there any query to move certain data from a sql data to access table through query. i am having a requirement where i have to fetch the records from a sql table that falls within a specified range to a ms access table. is this possible through a query.
thanks
I have two tables, one for cases and one for inventors. When I query a case linked to the inventor table I get as many rows as there are inventors. Is there a way that I can get just one row for the case and have the inventors lists Doe, John; Doe, Jane; Buck, Jack? I usually export results to Excel and my people want the inventors in one column for certain reports.
I would search for it but I don't know what it's called.
Thanks,
Don
i have 6 table in SQL Server and i have created one view and create single table by linking all the table,now i want to join two column like
Column A and Column B = Column C
e.g
A B C
Atul Jadhav Atuljadhav
Vijay vijayvijay
in above exambe column A having firstName and Column B having second name and i want to join this two column in C column "atuljadhav" and if column B is blank then it join A value tow timestriger code as it is auto update column and every time (update, append, modify, delete) it should be update automatic
I have several pdf files that need to be imported into the database. Each pdf file is to link to several rows in a database.
The files have different file names and keep coming every week. I need to import them into a table and link it with the data table. Need help on how to get the filename out of the pdf file and save it on the table with the image. I can use that filename as the foriegn key..Any help would be really great..
-Sri
Little puzzle this.I need to link from SQL2000 to Access to get to a table held within anAccess DB. Now you can link within Access to a SQL table, but can thisbe done the other way round ? It's the first time I've needed to dosomething like this.What I have is an application which was written (by me) with an Accessbackend (to run on a CD) and now it needs to run on SQL. However, Iwant to be able to swap over between backends so that I can choosewhere this runs from. I've done this with the sole exception of onetable. Previously most of the data was held in one MDB file and a linkto another (one local copy and one on CD). My queries use the link toquery both local and the CD copy data together.I can re-write things if I need to, but I was curious about there beinga way of doing this. What I want in effect is to query a view on SQL,but the data will be held in Access instead of on SQL.I suppose I can import as I need it as the data is purely read only. Idon't think this can be done, but wanted to check first. I can alwaysmove the remainder of the data over to SQL if needed. It's not a bigproblem, just something I'm curious about.Any pointers would be appreciated.Thanks in advanceRyanp.s. In case anyone wonders why I have taken this approach it isbecause we have a query tool and data that we send out to clients onCD. Some now want this web based, so I will publish the application ona Citrix server, and by changing a config file can swap within theapplication to point to SQL (by changing the ODBC settings). This way,I only need one copy of the application and can change backends as Ineed to.
View 2 Replies View RelatedHello,
I have a table called "ShoppingCart" which has fields for CartID, UniqueID and Quantity. At the moment I haven't specifed a primary key for this table becuase none of the fields are unique. I need to be able to link this ShoppingCart table to a table called "Size" using the UniqueID (both tables contain a field for UniqueID) in order to obtain the ProductID and CategoryID of the data item. I then need to link the ProductID and CategoryID I obtained from the first part of the SQL statement to a table called "plants" in order to get information from the following fields in the plants table: Common_Name, Latin_Name and Thumb_URL.
So far I've been able to use an INNER JOIN with the Size table to obtain the ProductID and Category ID using....
SELECT * FROM ShoppingCart INNER JOIN Size ON ShoppingCart.UniqueID = Size.UniqueID WHERE CartID = @CartID ORDER BY ProductID
I now need to be able to use the ProductID and Category ID I've obtained from this part of the query to reference the Common Name, Latin Name etc of the data item from the plants table. Is there any way that I could do this?
I do sometimes wonder if I'm making this whole thing a lot more complicated than it needs to be. At the moment I'm storing details about the different types of plants the garden centre sells in the "plants" table. However, some plants are available in different sizes so I've got a table called "Size" which links to the plants table using the ProductID and CategoryID of data items. The Size table has a unique primary key field called "UniqueID" which is used to uniquely identify every plant that the garden centre sells (this cannot be done using Product/CategoryID becuase different sizes of the same plant have the same ProductID/CategoryID). I'm then storing just the CartID, UniqueID and Quantity in the "ShoppingCart" table. There must be an easier way of structuring the whole thing!
I'd really appreciate any help you can offer me.
Many thanks,
Luke
I am looking for some assistance with an update query that needs to link 3tables:This query ran and reported over 230,000 records affected but did not changethe field I wanted changed, not sure what it did.I did notice that the "name" in "GM_NAMES.name" was colored blue in QueryAnalyzer. Is it bad to name a column "name"?UPDATE ABSENCESset CustomerContactID = cicntp.cnt_idfrom absences, cicntpwhere (SELECT cicntp.cnt_l_nameFROM cicntp INNER JOINgm_names ON cicntp.cnt_l_name = GM_NAMES.name INNER JOINAbsences ON cicntp.cnt_id = Absences.CustomerContactID)Next I tried this query which is still running after 75 minutes (on alaptop)update absencesset CustomerContactID = cicntp.cnt_idfrom absences, cicntp, gm_nameswhere gm_names.name= cicntp.cnt_l_nameAs you can see, the 3 tables are ABSENCES, CICNTP and GM_NAMES.Absences.CustomerContactID is what I need updated, when finished it shouldmatch CICNTP.cnt_idGM_NAMES is a temp table and matches records in CICNTP.cnt_l_nameCan some of you school this newbie on the best way to do this?Thanks a bunch!
View 4 Replies View RelatedCan someone help me with how I should set the relationship in my address tables and how they will affect the deleting of a client?
So, if I have:
tblClients ClientID
tblClientAddresses AddressID
tblClientAddressLinks AddressID, ClientID
Currently I have them set as a 1-M from tblClients to tblClientAddressLinks and a 1-M from tblClientAddresses to tblClientAddressLinks
If I try and delete a client and there address is shared wont this go very wrong?
So here is what happens when I try and delete a client:
The DELETE statement conflicted with the REFERENCE constraint "FK_tblClientAddressLinks_tblClients". The conflict occurred in database "DBSQL2", table "dbo.tblcLIENTAddressLinks", column 'clientID'.The statement has been terminated.
Here is what happens when I try and delete an address:
The DELETE statement conflicted with the REFERENCE constraint "FK_tblClientAddressLinks_tblClientAddresses". The conflict occurred in database "DBSQL2", table "dbo.tblClientAddressLinks", column 'Address_ID'.The statement has been terminated.
I am trying to link a table from a MS SQL database into access97 thru ODBC. When I do this, I get the message "Can't define field more than once." When I look at the table in SQL, there are no duplicate field names, however, when I bring the table in to do a Crystal Report, I see there are 2 fields that have duplicate field names 6 times. (Evidently Crystal doesn't care about this as Access does.) Any clues on what is happening?
View 2 Replies View Related