Accessing Views Via ODBC From MSAccess
Jul 20, 2006
Hello,
This started as a simple thing, allow somebody to access to a database on a SQL 2005 server so they could get to it from MSAccess. I have created a login (Windows Authentication) so they can see the database and gave them "public" and "datareader" abilities.
The user then informed me that while they could see the views they could not see the data. Not being a DBA (sorry) I added "public" to the view's permissions (under properties) and granted it "Select" access and nothing else. After I completed this the user could not even see the views to pick them (via the ODBC link from MSAccess) and when I try to remove "public" from the permissions it dissapears, but when I reopen the permission "public" is back.
I then find out there is no data in the views and so the user would not have been able view anything anyway.
So what I want to do is remove "public" from the views permissions, is this possible?
Thanks for any help,
Tyrone
View 4 Replies
ADVERTISEMENT
Dec 22, 2004
hi,
I have MS Access Database (test.mdb), and i have a table called tblEmployee in that.
How to query the table from SQL Server Query Analyser?
Thanks in advance
qAnand
View 1 Replies
View Related
Sep 7, 2007
Hi,I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?Thanks.
View 4 Replies
View Related
Apr 30, 2007
Is there anyway to access system views on/from a linked server?
I have unsuccessfully tried various permutations of
select *
from [MDEDATAWTDss2005].master.[information_schema.colums]
Thanks
View 6 Replies
View Related
May 28, 2008
Hi all, I'm setting up an ODBC connection for the UPS WorldShip application. I created a view for the shipment data in my database. The user account I'm using can only access the view, and when I try to connect it says there are no tables. Is there a way to make ODBC "think" the view is a table? TIA! - Mark
View 4 Replies
View Related
Apr 10, 2008
Hello,
There is a SQL Server databse installed in my machine.
I want to modify a data in a table.
How can I access the data with ODBC Administration?
Thanks
View 6 Replies
View Related
Nov 24, 2006
Hi,
I am migrating from MySQL 5 to SQL Server Express 2005 (SP1) and I am having some remote connections issues. I have tried just about everything that I have read in the forums and in Microsoft's knowledge base about remote connections with SQL Server. I am using Visual FoxPro 9 as my client and connecting with ODBC to SQL Server. The strange thing about my issue is that I can connect just fine with localhost or using my LAN IP address. Before I get to the errors, here is what I have configured:
Remote connections are allowed, TCP is active, SQL Browser service is active and running, my router forwards requests on ports 4567 (my custom port that I setup IP1 in SQL Server Configuration Manager to listen on), 1433 and 1434 (TCP and UDP for all) to my local IP address. My firewall will not block ports 4567, 1433 or 1434 (TCP and UDP for all). I even tried turning my firewall off.
For example, the following connection strings work. The first works when I am at the same computer as my SQL Server, the second works from a different computer on the same LAN.
Driver=SQL Native Client;Server=localhostsqlexpress;Database=xyz;Uid=adm;Pwd=pw
Driver=SQL Native Client;Server=192.168.1.3sqlexpress;Database=xyz;Uid=adm;Pwd=pw
When I try the following connection strings (with either my hostname or internet IP address, I get one of several failures):
Driver=SQL Native Client;Server=my.domain.comsqlexpress;Database=xyz;Uid=adm;Pwd=pw
The above strings yields this error message: [Microsoft][SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]
Driver=SQL Native Client;Server=my.domain.comsqlexpress,1433;Database=xyz;Uid=adm;Pwd=pw
Driver=SQL Native Client;Server=my.domain.comsqlexpress,4567;Database=xyz;Uid=adm;Pwd=pw
Using either the SQL default port or a custom port yields this error message: [Microsoft][SQL Native Client]Login timeout expired Connectivity error: [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.
The fact that I can connect with my LAN and not on the internet suggests that my ISP may be blocking port 1433. For this reason I tried to change the port my SQL Server instance was listening on but I have a feeling it isn't working.
Any help would be greatly appreciated as I've been stuck on this problem. Is there any way to test if my SQL server instance is actually listening on my custom defined port? Thanks in advance.
-Tom
View 1 Replies
View Related
Jan 12, 1999
We will be converting shortly from 6.5 to 7. We have a widely
used application that currently accesses the SQL 6.5 using SQL Server
odbc driver version 2.6
Will the 2.6 driver work with 7.0 and ff not, what will we have to do to
get it work with 7.0?
Thanks for the info.
Jerry Dunn
ViaHealth
jerry.dunn@viahealth.org
View 1 Replies
View Related
Mar 28, 2008
I have some tables sitting in schema "A", then I created a view in schema "B" based on the tables in schema "A". Then I gave the user just access to schema "B" to only run the view but not see the tables in schema "A".
Could it be that if the user tried to ODBC to that db, they would only see the view in schema "B" and not the tables in schema "A"?
Thanks!
View 2 Replies
View Related
Dec 13, 2005
Dear All,I am using the oracle transparent gateway connectivity with sql serverusing tg4msql,as far assettings are concerned those were set and Connectivity is working Fine,and getting the responsefrom that server.here is description what I done as:-There is a View on Sqlserver whichis Join of 6 tables and havedata around 1 million in 2 tables and 0.5 million in rest of thetables, as the Query for creatingsqlserver view is given below:CREATE view Account_anila_test asSELECT ......(around 50 Columns)FROM GE_Init.dbo.Person INNER JOINGE_Init.dbo.Konto ON GE_Init.dbo.Person.Person_ID =GE_Init.dbo.Konto.Person_ID INNER JOINGE_Init.dbo.Produkt ON GE_Init.dbo.Konto.Produkt_ID =GE_Init.dbo.Produkt.Produkt_ID LEFT OUTER JOINGE_Init.dbo.CRMKonto ON GE_Init.dbo.Konto.Konto_ID =GE_Init.dbo.CRMKonto.Konto_ID LEFT OUTER JOINGE_Init.dbo.calcKontoOBSdt ON GE_Init.dbo.Konto.Konto_ID =GE_Init.dbo.calcKontoOBSdt.Konto_ID LEFT OUTER JOINGE_Init.dbo.calcKonto ON GE_Init.dbo.Konto.Konto_ID =GE_Init.dbo.calcKonto.Konto_IDWHERE (GE_Init.dbo.Konto.SlettetKonto = 0) AND(GE_Init.dbo.CRMKonto.MarkertForSletting = 0OR GE_Init.dbo.CRMKonto.MarkertForSletting IS NULL)Now,I made a table in oracle adjacent to sqlserver View and Inserteddata as:INSERT INTO ACCOUNT_TEST11(.....)SELECT .............FROM Person@dbl_getgc1 a INNER JOINKonto@dbl_getgc1 b ON a."Person_ID" = b."Person_ID" INNER JOINProdukt@dbl_getgc1 c ON b."Produkt_ID" = c."Produkt_ID" LEFT OUTER JOINCRMKonto@dbl_getgc1 d ON b."Konto_ID" = d."Konto_ID" LEFT OUTER JOINcalcKontoOBSdt@dbl_getgc1 e ON b."Konto_ID" = e."Konto_ID" LEFT OUTERJOINcalcKonto@dbl_getgc1 f ON b."Konto_ID" = f."Konto_ID"WHERE (b."SlettetKonto" = 0) AND (d."MarkertForSletting" = 0 ORd."MarkertForSletting" IS NULL);This insert Satement worked fine,but when I inserted from the viewalready created in SQLSERVER as:-INSERT INTO ACCOUNT_TEST11(.....)SELECT .............FROM Account_anila_test@dbl_getgc1;This gave a following error:-"WEB_30_SUM", "WEB_360_COUNT", "WEB_360_SUM", "WEB_90_COUNT","WEB_90_SUM"*ERROR at line 48:ORA-28500: connection from ORACLE to a non-Oracle system returned thismessage:[Transparent gateway for MSSQL]ORA-02063: preceding 2 lines from DBL_GETGC1Elapsed: 00:01:01.04after this I set The paramater "HS_FDS_TRACE_LEVEL=ON" in INIt file oftg4msql Folder then in thetrace File of tg4msql folder following error comes:-(0)(0) [Microsoft][ODBC SQL Server Driver]Timeout expired (SQL State:S1T00; SQL(0) Code: 0)(0)If any one has Faced the similar kind of problem and Can help me,Pleaselet me Know where I amdoing Wrong and How this error can be ractified.Waiting For Reply ASAP.RegardsLovkesh
View 1 Replies
View Related
Nov 6, 2007
I have a report that accessed a Visual FoxPro 6.0 database via ODBC. Since I upgraded to Visual FoxPro 9.0, now I am getting the error:
Query Server Error
DMS-E-RBI_TABLE The table or view <table name> was not found in the dictionary
I've verified that the right path is setup and I've tried installing about every driver and none of them are working. I created a new database in 9.0 and the I can access it fine. Also, there are old tables that are accessible in the same path, just not the ones I need. Any ideas would be helpful..
View 1 Replies
View Related
Oct 23, 2007
Hi
I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.
How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?
I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).
I'm stuck - help!
View 4 Replies
View Related
Mar 12, 2008
We are experiencing failures when accessing a datatype="Attachments" field in a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.
We are using an MS Access 2007 database
ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these drivers)
DB contains a table with a field of type "Attachments" (which is new in MS Access 2007)
DB contains a query that selects the fields of the above table
Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The "Attachments" field is displayed as the file name of the attachment(s).
However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT the table) results in either incorrect results or unexpected failures of the ODBC/OLEDB drivers.
Error using ODBC (using "ODBC Test"):
=============================
select * from Query1 does not give an error, but displays a "1" for the Attachment field.
select Attachments from Query1 gives the following error:
stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97,
*ColumnNumber = -2, *RowNumber = -2
MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|);
there is no message for this error."
Error using OLEDB (using "RowSetViewer"):
================================
select * from Query1 gives the following error:
Interface: Unknown
Result: 0x0004001 = E_NOTIMPL
FormatMessage: €œNot implemented
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CRowset.cpp
Line: 616
select Attachments from Query1 gives the following error:
Interface: IID_ICommand
Result: 0x0004005 = E_FAIL
IErrorInfo: [0x0000f3f1] €œUnspecified error€?
File: F:DepotSQLVaultmdac28sdkSamplesoledb
owsetviewerSDKobji386CCommand.cpp
Line: 439
If it would help to analyze the problem, I have a folder containing all pertinent files (bare-bone database, tools, instructions) to reproduce this. I could attach it as a ZIP file, if requested.
Thanks,
Joe
View 1 Replies
View Related
May 18, 2007
hi,
I maked ODBC for SQL erver data base and linked with Access database which is working good but when I check ODBC link in access it shows alot of other stuff and all tables related to that database .
Is there any way to remove irrelevant tables/views etc from that ODBC - thanks for help
AA
View 2 Replies
View Related
Jan 3, 2007
Hello,
We have allowed one of our clients to create views in our .net /sql application so they can build reports using MS Access. They have been doing so successfully for years. However lately they are experiencing Timeout Expired messages when in the actual web application. I have confirmed that several of the views when opened in Access do cause blocking, the locks are promptly realeased when the the access odbc of the sql view is closed.
When the views are opened in SQL query analyzer, there is no sql blocking.
Would this be related to how the tables are linked?
Any suggestions would be greatly appreciated.
View 3 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Aug 6, 1998
Can any body tell me the step by step procedure of porting MSACCESS tables into
SQL SERVER
thanks in advance
Rakesh
View 2 Replies
View Related
May 21, 2004
I'm using MSAccess 2k as a front end to a SQL Server 2k database.
I have a user set up in SQL Server to be database owner for a specific database.
Using NT authentication.
Using ADO.
The MSAccess application executes a store procedure on the SQL Server that (1) deletes a tables if it exists, (2) creates and populates a table (the owner shows up in Enterprise Manager as the user name) and then (3) uses the table (read) in a join to do some other processing.
The (1) delete works fine.
The (2) table is created and populated fine.
On (3) the Select (inner join) to do the aditional processing I get an error message that the table name used is an invalid object.
Workaround.
If I create the table as sa separately and in the store procedure, instead of deleting, creating and populating the table I merely truncate the table and then repopulate it, everything works fine.
I would prefer to not have the table sitting around the database all the time.
Anyway, I'd like to figure out why the table is causing a problem.
Any ideas?
Thanks.
View 1 Replies
View Related
Jan 16, 2006
Hi!!
I´m having a problem setting up a job in sql.
When i run my DTS is on succed but when I schedule the job --failed
Is connecting a sql serve db with msaccess you.
Sombody know? if i have to consider something??
View 2 Replies
View Related
Mar 28, 2008
Hi,
I am getting below error when exporting data from SQL Server 2005 to MS Access.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (1706) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Its direct mapping from OLE DB Source (SQL Server 2005) to OLE DB Dest (MS-Access Database)
Help from anyone. Plz its urgent.
Regards,
View 3 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Jul 12, 2004
I would like to pull some data from a SQLServer database, and save it into an Access MDB file (which can be empty to start). I would then zip up the MDB and download it to the user.
I am seeking advice on the most "elegant" or "efficient" way to do this. Here are some ideas I have been considering:
1) Should I start with an empty template MDB and file-copy it before I populate it? Or is there a neat way in ASP.NET to allocate a brand new MDB outright?
2) I could read the SQLServer data into a Dataset object. I could then open a connection to the MDB, create a table object, defining all the columns, etc., and then I could write the data to the new table object. BUT ... I have a hunch there is a nifty ADO.NET way to save the data already in the Dataset object right into the MDB (creating the table and columns as a matter of course) ... all with an instruction or two (or three). Any ideas?
Thanks in advance!
View 1 Replies
View Related
Apr 28, 2004
Hi there,
We have a MSSQL 2000 db, with several MSAccess 2000 adp interfaces connected to it.
Several bit data type fields of the tables are updated through those interfaces, but from time to time (I can find no pattern when it happens), flagged fields (i.e. with value 1) are unflagged (they get value 0), without any user interaction on that field (neither in the tables, neither through the checkboxes).
Anybody familiar with this issue?
View 1 Replies
View Related
May 28, 1999
The following works fine in Access:
SELECT Hist_POS.POSITION_CLASS_CODE, First(Hist_POS.POSITION_TITLE) AS FirstOfPOSITION_TITLE INTO [Manhour Positions]
FROM Hist_POS
GROUP BY Hist_POS.POSITION_CLASS_CODE
ORDER BY Hist_POS.POSITION_CLASS_CODE;
But not SQLServer because of First(). How can I achieve the same results in SQLServer?
Thanks
View 1 Replies
View Related
Mar 18, 2004
I am having issues with MSAccess and MS SQL for a new client. It worked in the past, but I cannot talk to the old computer guy?!# Here's the scoop. It uses an Access front end and ODBC to conect to the MS SQL 7.0 server. In the office they use a system DSN named DATA to connect using NT authentication. I've got the firewall configured for the two employees static ip addresses to goto the MS SQL server. On the remote machines, I have added a system DNS named DATA as well that uses MS SQL authentication. I setup the remote user accounts on the MS SQL server. Now heres the problem. I can test the ODBC from the remote omputer and it works.At first I could not get into teh database at all. I went in and updated the Access file connection strings to include a username and password. Now I can get into the Access front end and access the main page. (IT has a couple of buttons - Shpping, Inventory, Management, etc.) I can even click on these buttons and pull up the next page. (For example I can click on the management button and pull up a list of new buttons- one of which is Auto Exec Bob) NOw when I click autoexec bob or any of the others it takes a while and then give me an error. (SQLState 28000 MS SQL error: 18452 Login failed for user null. Reason: Not associated with a trusted SQL Server Connection.) If I click ok then I have a box that lets me uncheck the use trusted connection and sign in using my remote username and pass that I setup. Eventually I can access everything - one step at a time.Each time it tries to connect to the database it takes about thirty seconds to time out, then gives me this error message and I can log on again. Any help to sort this out would be greatly appreciated. Thanks in advance.
View 1 Replies
View Related
May 30, 2006
Hitaishi writes "We have recently migrated from MSAccess as our back end system to SQL Express, but the dialogs showing records of the table which were taking lesser time in Access are taking more time in opening and doing any database operations.......
We are using .Net Framework 2.0. Does it have anything to do with lowering down the performance ?
Any help would be appreciated."
View 1 Replies
View Related
Jan 26, 2007
Migration from MsAccess to Sql-Server.
Problem:
Ing_ID = nz(me.Ing_AutoNr,0)
me.openform "MyForm",,,"ID = " & Ing_ID
This work in MsAccess with new records but this doesn't work in Sql-Server. How can I solve this?
View 1 Replies
View Related
Apr 26, 2006
I am trying to use SQLServer for the first time and I am trying to use the upsizing wizard on Access to get moved over to SQLServer. I am getting the following error messages:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server][Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQLServer Error: 17
[Microsoft][ODBC SQL Server][Shared Memory]SQL Server does not exist or access denied.
Does anyone know how I should proceed? ( I am new at this so please be gentle.) Any help would be appreciated.
Thanks
View 3 Replies
View Related
Mar 8, 2007
im in the begginging stages of converting an access 2003 backend to SQL 2003sp3.
The access's upgrade wizzard did an ok job getting most of the tables ported over. Now when the frontend relinked the tables to SQL server im assuming i just continue on using them as is. When the wizzard wanted a connection to use i created a new connection and saved it on the server. Im assuming this connection object is what access is using to get to SQL.
Am i assuming correctly ?
The reason i ask is only a few of the linked tables in to sql seem to be editable. some are read only ? (i have primary keys in all my tables.) im just calling a query bound to the linked tables.
Secondly, But could be related.
Now this problem i dont understand. On open of my main form i log who and what pc they are using. in this situation i open a record set, log the data and close it. At first the error was i needed to add dbSeeChanges on my object, ok so i added it as an option.
But i now get an the error message: at rsTran.AddNew "Run Time error: 3027 , Can Not update. Database or Object is read Only ?
here is the code
Set db = CurrentDb
Set rsTran = db.OpenRecordset("tblUserLog", dbOpenDynaset, dbSeeChanges)
struser = Currentuser
strNetwork = atCNames(1)
strPC = atCNames(2)
rsTran.AddNew
rsTran("UserName") = struser
rsTran("MachineName") = strPC
rsTran("EmployeeNum") = strNetwork
rsTran("Notes") = "User Logon"
rsTran.Update
Set db = Nothing
Set rsTran = Nothing
thank for any advice.
jon
View 1 Replies
View Related
Dec 6, 2006
We have plan to migrate our database from Access to SqlCe.
Here our situation:
We will never use a Pocket PC applications, only Windows desktop application.
We want to migrate the .mdb file to a .sdf file (I've seen the ADS application, but since we don't use mobile devices, it's no use)
The .sdf will be use on local client machine only.
Here's my problems:
I've use a conversion to migrate from access (http://www.primeworks-mobile.com/Downloads/DPW.html) and seem to work fine.
I updated the source code to use the SqlServerCe dll instead of OleDB.
Some of my queries work fine, but others just won't work in VS2005 using C#.
To check if the query is really wrong, I take exactly the same query and put it Query Builder from the Server Explorer in VS2005.
And the query just work fines!!! It's bring back all the records asked for, and I've got an error for the same query from my source code.
I'm using a datatable, here's the code used for filling the DataTable. I've got an error on the line adapter.FillSchema()
string connectionString = @"Data Source=" + fic_parent.CheminFichierComplet;
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
connection.Open();
SqlCeCommand command = new SqlCeCommand(requete, connection);
command.CommandType = CommandType.TableDirect;
adapter.SelectCommand = command;
tableResultats = new DataTable();
adapter.FillSchema(tableResultats, SchemaType.Source);
adapter.Fill(tableResultats);
// Close connection
connection.Close();
}
Error is:
Message="The specified table does not exist. [ (...) ]"
Source="SQL Server 2005 Everywhere Edition ADO.NET Data Provider"
HResult=-2147217865
NativeError=0
Here my questions:
Is it possible to have an application that will convert my Access databases to an SSCe .sdf file ?
Why I can query in Server Explorer and can't in my source code using the same sql statement ?
At which level my code isn't good, because I just replace all OleDB* --> SqlCe* in my code.
I hope that I explain clearly my problem, do not hesitate to ask me questions about this problem.
View 8 Replies
View Related
Jul 10, 2006
Hi,
I Need to make a script or task that programmatically export data from SQL Server to Access File , and how to call the script from the .NET?
View 1 Replies
View Related
Mar 24, 2006
How run SSIS package from MsAccess?
View 3 Replies
View Related
Feb 9, 2007
I have a Access 2002 (saved as 2K format) front end to a SQL Server (2000 I believe) and everything worked fine until I added 30 new fields to the main table. All of these fields are data type Bit and all are initialized to zero. I can add new records without any issues. The problem is that any time I try to update or delete a record I get the dreaded "Write Conflict..." msg in Access. If I use an older version of the system without refreshing the linked tables I have no problems. This behavior is also exhibited if I try to update/delete a record directly in the table so I know it is not something in my forms. Any help would be greatly appreciated.
Ron
View 1 Replies
View Related
