Our env: SQL*Server on win2k server.
Question is: I have table1 in first db and I would like to access this table in second db. In oracle, we create db links to achieve the same. How do we achieve the same in SQL Sever?
Any help is greatly appreciated.
Access Connection
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!
Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
Any ideas please help
thanks in advance
Dave
Hi Everyone,
I have set up a link from ACCESS to a SQL 7.0 database using ODBC (File DSN saved on a shared DRIVE). The link works well only from the workstation where the link was created. But How can I create a link so a group of users can view the linked table in ACCESS without type a password? Any suggestion is appreciated.
Lunjun
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,
Hi all,
Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table. What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field? Thanks a bunch for any asistance.
Bill
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
Another question about SQL Server tables and access grants.I've created an user 'user1' as Access account, ad also as db_owner of aselected database.Then, I've created a table as user1.table1.When I try to access the data of the table, with the account name of user1,I must specify the owner ('select * from user1.table1') even if I've loggedas user1 (I need to access the data just with 'select * from table1').What's the problem?Thank youFederica
View 2 Replies View RelatedHi
Iam using c# windows application.
I have a database like DemoData . It contains some tables?
I want to allow the updations and editions for the fields of table1 in DemoData into Outlook calendar.
My process is.....
1. I want to access the Outlook2003 Calendar fields like Subject,Location,StartTime ,EndTime and Contacts and Categories etc and to place them with the DemoData table1 fields using C# windows Application.
Letus assume the Table1 contains the columns as..
Subject,Address,Stime,Etime, Contact and Cate
2. same as the above question i want to access the Tasks in Outlook2003 with another table fields.
so if we want to work like this what we have to do?
Please give me suggestions...
Thanks and Regards,
I am trying to access the fields in a table using Visual Basic in Web Developer .NET
View 1 Replies View RelatedI have some junk tables in my database, but I am not sure if they are really not in use. Does sql server log the last time that a table gets accessed? when I say access I mean any actions toward the table, including select, update, insert, etc.thanks in advance.
View 2 Replies View RelatedHi All,
I have to to do SQL Query a table which is placed in different server than my current SQLServer.Please guide me to perform this task.
Thanx,Karthik.A
Hi:
I have a UDF that splits a string based on a delimiter.The UDF returns a table of records:1- ID2- ValueThe string I am sending to split is:'2-4-07:00 AM-08:30 AM'I am splitting on '-'What I get back is a table of 4 rows.I want to get each value, convert it to the right data type, then assign it to its own variable.How can I get each row alone manually? Can I? Without using Cursors?if I do a "select * from Split(@myData,'-')", then I cannot retrieve each field alone,Thank you.
My problem is that I can't query a couple of tables. I created them using DTS myself, in a db called contracts, and called one of them 010425. I've logged in as SA, and making sure the correct DB is shown in Query Analyser.
Here's the query...and very simple it is;
SELECT TOP 10 * FROM [dbo].[010425]
I just keep getting the following error message;
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.010425'.
But it shows up in Sysobjects and EM!
I can query the system tables, using EXACTLY the same syntax...no problem there.
I've got SQL Server 7 running on W98. Everything is happening locally.
Any ideas what I'm doing wrong, people?
Thanks in advance for your help,
Jaishel
Hi all,
I'm writing a procedure that require to access a table from a different database but from the same server. Can the procedure able to read this table. If so how should my procedure use that table?
Thank you for all your help.
I have an access application that gets used by people on the road.
They put data into a local table and occasionally log into our network and transfer a file to a temp table in SQL.
How is the best way to approach this?
I've created a linked table to Access. I want to be able to delete records and modify existing records through this linked table. I used the account with sa privelleges yet i am unable to delete/update any records.
Does anyone know if there is any fix or am i doing anything wrong ?
Is there a way of INSERTing INTO an Access table using T-SQL? Here's an Access query that I need to do in SQL Server:
INSERT INTO tblCreditMemos
IN '\NW-ITD-FS2ilcas$AccessAutomated Budget ReportingABRBackEnd.mdb'
SELECT * FROM billc_cred03f_temp;
Any help appreciated.
I'm using ODBC to link a SQL table to access and using Access as the frontend, so to speak. Trying to write a query, but I get Timeout Error.
I need to resolve the timeout error, I've tried several things in the ODBC
configuration, but no solution yet.
thx
jm
hi,
i am using stored procedure which put data in another table fron different database.
so i am using servername.databasename.dbo.tablename.
if I am change the servername then i have to edit the Stored procedure.
Is there any way to access server name.so that if server name is chane then also i dont have to change stored procedure.
Like in c we use #define where we change value of variable that is reflected in whole program.
Hi,
I have 2 databases in SQL Server named Database1 and Database2. I have logged in Database1. I would like to see the records of the Table which is present in Database2. Could anyone please tell me How to access the tables in Database2 from Database1?
Thanks,
Sathish kumar D
Sathish kumar D
Hello,
First time poster.
I have a database design question. I just read the article on table inheritance but i still do not know if I'm doing this right.
Say you have an organizations table and a persons table like the ones below with a many to many relationship like these three tables:
Organization:
AutoId TaxId Name
Person:
AutoId FirstName LastName
OrganizationsPersons:
AutoId OrgAutoId PersonAutoId
Now the question is that an organization and a person both need to access the address table.
Is it better to have a PersonsAddress table and an OrganizationsAddress table, or is it better to have some sort of AddressAccess table that the persons and the organizations tables both relate to and have some sort of type field in there that specifies Org or Person?
Thanks ahead of time for any help.
Hi,
Lets say i have 2 servers in my Computer. Local server and a common server (network) and i want to copy a table from common server to my local server . How to do that?.
I want to import an Access table into a SQL table. Some of the integer Access fields have a null value, but when they are imported I want them to be 0 or zero in my SQL table. How can I do this please ?
View 3 Replies View RelatedMorning/Afternoon Hello all,
Have little question to ask; well that's the world’s biggest bluff.
I'm currently looking after a highly used database, but i know that it is full of tables that are not being used (Need deleting) as it’s taking up so much space that can be freed up; basically these tables have been left behind by developers. I did go ahead and think about just randomly deleting them but then i thought that some of these might still be used.
So here's the question is there a way that when a table is access as in an update, select, delete that i can record it in a table along with the date it was accessed (and even what type of access it was I know this is pushing it) and then query that table at a later date so i can tell what are the true tables being access and then hopefully go ahead and delete the correct ones and not make a fool of myself.
Any help or ideas on this would be a great help for me. I am still quite new Sql Server.
Hope you can help
One of my user gets the following error from MS Access running from his local machine and linking to the database & appropriate tables. The MS Access application uses back end SQL server to get info from. ODBC connections are fine, I tested them already. His machine uses DHCP to connect to the network. Recently he got new upgraded PC and since than this problem arose. He was OK on his older PC. No changes has been made on sql server side. One thing I'd like to mention is: We have Citrix server on which this application is published and this user can connect and perform the same tasks using the Citrix client. He gets this error only when he tries to run it on his local PC and connect to the database on a remote server. Any ideas what & where else to look?
Thanks
Red X Error = There was an error linking table ~tmpclp554322. For further assistance contact the help desk.
Hello,
Now, that when I open a access I need to go to Data -> Databse Utilities - > Refresh Table Manager. Then I select which table to refresh. Is there a way to make it auto refresh when I the database?
Any help would be greatly appreciate.
Thank You
I add a table to sql studio express, but i cand see it in access backend in the other computer.
What is wrong?
Thanks,
Azi
I have an MS Access 2003 database from which I want to seek a specific record in a SQL Server Express 2005 database. I can connect to the table and get a recordcount but the recordset.supports (adseek) and recordset.Supports(adIndex) both return false. Any suggestions? Specific code I'm using is as follows:
Dim cnxn As ADODB.Connection
Dim strCnxn As String
Set cnxn = New ADODB.Connection
cnxn.Provider = "sqloledb"
strCnxn = "Data Source=SERVERSQLEXPRESS2005;Initial Catalog=RAMPSQL;Integrated Security='SSPI';"
cnxn.Open strCnxn
Set rsWSC = New ADODB.Recordset
rsWSC.CursorLocation = adUseServer
strSQL = "DailyData"
rsWSC.Open strSQL, cnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
Thank you!
Hello
With SSIS I can create a TABLE in MS Access using OLEDB connetion type and SQL. - no problem
The Query looks like CREATE TABLE 'MyTable' (.....)
Also I have no Problem to delete the content of MyTable.
But how can I check if the TABLE 'MyTable' exists in the Access DB?
How can I drop the TABLE 'MyTable' in the Access DB?
Thanks for an early answer
Regards Chaepp_1
I have an access database that i upsized a few weeks ago and i need to add another table form that database, however when i use the DTS import wizard to get the particular table i wish i get as far as picking the table but i cant preview or do the import, i get the following error message on it!
Error Source: Microsoft Jet Database, Error Description: record(s) cannot be read; No permission on the table 'InternetBilling', Context: error calling openrowset on the provider.
Ive working on a copy of the access database and given all user groups read persmission and im still getting this, how do i get around it?
With much thanks for any help,
Amit
Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!
View 2 Replies View RelatedCan I programatically access all relationships for a sql table? I know i can do this in the table designer ...
---Jim