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 any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
Can anyone tell me how to link to a oracle user? DO I use a predefined DSN? Do I just specify the server and the user and password?
Your assistance is appreciated,
David
I need to query both Oracle and SQL Server tables for reporting and processing, ....no updates, insert or deletes.
Is creating a linked server the only way? My company does not want me to see the entire database.
Can you just link tables? Like Access or Excel does?
Any articles or links would be appreciated.
Thanks,
Mike
I'm trying to set up an Oracle database as a linked server in SQL server 7 but having some problems.
The client software is installed and seems to be working fine (Used access to link some tables). I set the product name to Oracle 8.1 and the Datasource to the system dns name. I left provider string, location and catalog blank. Under the security tab I had it set to map to the oracle account and also tried the impersonate option, but always got the same error.
Error: 7399 OLE DB provider 'MSDASQL' reported an error. Driver's SQLSetConnectAttr failed]
ORA-12640: Authentication Adapter initialization failed]
This is my first time trying to set up and Oracle database as a linked server, any help is appreciated.
Has anyone successfully linked to an Linux Oracle server running 9i? We are unable to get the link working.....have installe dvarious drivers and still get connection refused. Any thoughts?
View 1 Replies View RelatedHi,
I was able to link SQL Server Express to Oracle views using Linked Manager. However, when I run the query, the performance is very slow.
Is there a way to improve performance in querying?
Previously I was using Access to link to Oracle view. But the performance is not good. Takes about 8 hours for approx 6000 records.
Thanks a lot,
Stara
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
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
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 RelatedI 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 Related
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
After 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!
Looks 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
Hello, I'm having a problem connection to an Eval version of SQL Server 2000. I'm pretty sure the problem is not due to Eval version since...well, I need to evaluate the server if I intended to purchase it right? anyhow. I've loaded a copy of SQL server 2000 on my local machine. I only have one machine which I use for development and housing SQL Server.
I tried to set SQL up real "vanilla" and elected to use only Windows Authentication, this bypassing any userdefined passwords for the sa login.
Now...I've written a real basic Stored Procedure to test the connection and verify proper data transfer from the .aspx page. here's the SP SQL code and the .vb code behind page as well as the error I get. Note. I also tried Query Analyzer and was getting an error there as well indicating the SP could not be found...hmmm..
stored procedure:
CREATE PROCEDURE [dbo].[NewMemberInsert]
@UserNamevarchar(100),
@UserPassvarchar(10)
AS
INSERT INTO EEMemberList ( UserName, UserPass )
VALUES ( @UserName, @UserPass)
GO
.vb code behind
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents lblUserName As System.Web.UI.WebControls.Label
Protected WithEvents lblUserPass As System.Web.UI.WebControls.Label
Protected WithEvents txtUserName As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents frmMyForm As System.Web.UI.HtmlControls.HtmlForm
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents sqlCmd As System.Data.SqlClient.SqlCommand
Protected WithEvents txtUserPass As System.Web.UI.WebControls.TextBox
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()
Me.sqlCmd = New System.Data.SqlClient.SqlCommand()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=THIRDEYE;initial catalog=SQCTEST;integrated security=SSPI;persist sec" & _
"urity info=False;workstation id=THIRDEYE;packet size=4096"
'
'sqlCmd
'
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
sqlCmd.CommandText = "dbo.[NewMemberInsert]"
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
sqlCmd.Connection = SqlConnection1
sqlCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
sqlCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserName", System.Data.SqlDbType.VarChar, 100))
sqlCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserPass", System.Data.SqlDbType.VarChar, 10))
sqlCmd.Parameters("@UserName").Value = txtUserName.Text
sqlCmd.Parameters("@UserPass").Value = txtUserPass.Text
sqlCmd.Connection.Open()
End Sub
End Class
Now...the error
Server Error in '/EEscape' Application.
--------------------------------------------------------------------------------
Login failed for user 'THIRDEYEASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'THIRDEYEASPNET'.
Source Error:
Line 54: sqlCmd.Parameters("@UserPass").Value = txtUserPass.Text
Line 55:
Line 56: sqlCmd.Connection.Open()
Line 57:
Line 58:
Source File: C:InetpubwwwrootEEscapelogin.aspx.vb Line: 56
Stack Trace:
[SqlException: Login failed for user 'THIRDEYEASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
EEscape.WebForm1.btnSubmit_Click(Object sender, EventArgs e) in C:InetpubwwwrootEEscapelogin.aspx.vb:56
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
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
Frustration has gotten the best of me on this one. Can anyone help?
I need to pass the current value in an Access Data Project (back-end is MS SQL) text field to the where condition in an SQL using VBA. This is what I have, but does not work.
Private Sub Command44_Click()
Me.SS.SetFocus
Dim strSQL As String
Dim strSSecurity As String
strSSecurity = Me.SS
strSQL = "Update Employees Set employees.PositionID = '',employees.jobcode = '' Where employees.ss = strSSecurity"
DoCmd.RunSQL strSQL
End Sub
It is supposed to take the current Social Security number from the form and match it against the employees table. Once it finds the matching record it should update the PositionID and JobCode fields to '' (empty string)
But it doesn't
Anyone with any ideas?
Thanks
Dan
Hi there...I use SQL server integrated security so when a user opens a database inaccess it prompts the username & password in a small popup box onconnection, but I'd like to use my own customised form for theauthentication process, is this possible? I do know that this login popboxis displayed before any forms are loaded, can it be said that on databaseconenct that the form is opened? How will I transfer the values entered intothe login form to the sql server for authentication?Thanks alot in advanceRudi Groenewald
View 3 Replies View Related