November 3, 2007 at 3:54 pm
Thanks JJ,
It seems I can't figure out the connection string but may be this is because I'm using an ADP.
November 5, 2007 at 7:47 am
If you're having trouble seeing table and view objects, it's probably because the default schema of the user is NOT the same as the schema of the objects. Open SQL Managment studio and you'll probably see that the objects are dbo.* and if you open the User that is having trouble (under database,security, users) and check their default schema it's probably NOT dbo.
If you use NT authentication in your connect string, and set the permissions in SQL server, you should not have to deal with usernames and passwords on objects in MS Access. In my past expereinces many years ago, Access correctly used the permissions on SQL server objects.
Good luck
November 5, 2007 at 9:14 am
Thanks Doug,
The trouble happens when I try to assign the "SourceObject" of a subform as some view/query. I tried to use "dbo.*" and "SomeUser.*" schemes but may be didn't try to change the default schema of theobjects to a matching one.
I am using NT authentication in my projects but need to look into the code to explain better where the problem with constructing a new connection is.
November 23, 2007 at 9:03 am
This is a great article, and accurately describes a process that I used to do regularly. But today I tried to use it, linking my Access application to a new table I had created in my SQL Server database, and it didn't work! In fact, it failed on BOTH my machines!
The failure point came when I chose "ODBC Databases" from the drop-down list. At that point, the "Select Data Source" dialogue box failed to show up. And the "Link" dialogue box disappeared!
I am at a loss to understand what is going on? Please can anyone offer me any ideas?
November 26, 2007 at 9:01 am
Try to isolate the ODBC issue. Open ODBC from control panel and try to setup a machine datasouce for one of your databases. If this does not work, then take it from there.
Othewise, if ODBC works outside of MS Access then make sure you have admin rights on your PC and try to debug this. Please post the fix once you find it, so that others will benefit.
Good Luck
June 24, 2008 at 3:21 am
Hi.
Does anyone know if its possible to link between Access AND MS SQL 2005 without using ODBC?
I'm having an Access DB which was linking to and old MS SQL server, where ODBC was allowed but on the new SQL server it is not.
June 24, 2008 at 7:20 am
Jan, in Access 2003, it looks like ODBC was the only viable choice. I'm puzzed though as to why you cant link to the new SQL 2005 server. Since you are most likely running access as a client front end, I see no technical reason why this would adversely effect the SQL 2005 box. Can you post the reason why you cannot use ODBC on the client side?
Hope this helps
Doug
June 24, 2008 at 9:47 pm
Hi.
The reason why I would like to use something else than ODBC is because in the company where I'm working they wont use it anymore.
As I wrote, I've get an task where an SQL database was upgraded from MS SQL 2000 to MS SQL 2005 and there is an Access file that using data from the SQL server.
I tried to use SSIS to create a new Access file and got the data from the new SQL server and was able to make a query, but if new data is being added to the SQL server they aren't automatic updated in the Access database.
I was thinking about creating a connectionstring in vbscript instead of the ODBC - is that possible? and how?
I created a SSIS task that updates the access database, but I would like to avoid running several tasks just to update the Access database.
Is it possible to execute the SSIS tasks everytime I opens the Access database?
As you can see I haven't been working that much with Access but I'm hoping that it makes sence what I'm asking about.
\Jan.
June 25, 2008 at 8:49 am
Jan:
Does your company object to ODBC itself or to DSNs? It is very possible to connect from MS Access to SQL server without using DSNs (but still using the ODBC *driver*). There is a great website out there that talks about DSN-less connections. I've gotten a lot out of it in the past, but can't find it right now. Here's a website that I haven't checked out in detail, but looks promising: http://www.carlprothman.net/Default.aspx?tabid=81
Also, Microsoft has some web pages dedicated to the topic. I'd also recommend searching on 'dsnless connection' or variations to see what you can find.
You need some kind of connection protocal, whether it is ODBC or OLE DB. When you use SSIS to connect to a .mdb file, you are using some kind of software/driver that allows that connection, whether you use DSNs or not.
I can't figure out why a company would object to using a standard ODBC driver. However, if your company truly objects to ODBC period (and not just DSNs), the company should be fine with OLE DB. It is my understanding that OLE DB is MS's current connection software of choice. (There could be something even newer, but I don't think so given the way the new SSIS is set up.) The question then is whether you can use OLE DB from within a .mdb file to connect to SQL Server. I'm not sure. I haven't had a need to look into it.
Good luck,
- JJ
June 25, 2008 at 9:14 am
That's the thing that I just don't get: how could a company object to ODBC? It's not the best, but it's certainly one of the most useful tools that we have, I've been using it and Access ever since SQL Server 4.21. I have people throughout my enterprise using it all day long. It's solid.
I wonder if someone is deliberately spreading misinformation or touting a specific agenda to go to OOPs/OODB or something? It makes no sense to me.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
June 25, 2008 at 9:25 am
Wayne: I'm totally with you on that. My first thought is that Jan's post language or maybe her company managers are confusing ODBC with ODBC DSNs. That's why I gave the reply I did.
I can understand why a company might want to veer away from DSNs. The "security" features out there make it harder and harder to work with DSNs. On the other hand, I find DSNs convenient and don't see a major problem with them. Once, having a separate DSN allowed me to problem solve a connection problem that would have been extremely difficult to solve if a simple connection string had been embedded in my table or query defintion.
My apps create the DSNs automatically if the DSN doesn't exist on users' PCs. Thus, in my case there is no ODBC hassle for anyone when a .mdb is installed on a PC.
June 25, 2008 at 11:40 am
The only valid objection to ODBC that I can think of is the maintenance of the DSN on the client machines, and maintenance of ODBC drivers on machines. This can be a nightmare if you have hundreds of machines.
In access 2003 I did not see the option to create linked tables using OLE DB. I don't believe ODBC drivers install with MDAC so I'm guessing that's why they don't want ODBC.
However, I'd argue that what you're doing is worse by copying data from SQL server to access. First, I'd have to ask (1) how many machines need the access application.
Good luck Jan
Doug
Viewing 12 posts - 61 through 71 (of 71 total)
You must be logged in to reply to this topic. Login to reply