November 2, 2007 at 9:35 am
Wayne,
Very nice points about Access reporting.
I wonder if you could answer a question for me: Do you know if Access 2003 connects with SQL Server 2005 properly? I had heard that Access' ability to connect to SS2005 had been quite drastically compromised. If that's the case, then our ability to create Access reports from SS2005 data goes up in smoke.
Sincerely,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
November 2, 2007 at 9:44 am
Not extremely excited by Access articles in a SQL publication...
November 2, 2007 at 9:45 am
Depends on what you mean by properly. Basically, there is no difference. When I was testing this out, I could switch cleanly between SQL 2000 and SQL 2005(native) and the application didn't seem to notice.
The BIG problem you will hit with 2005 and Access (2003 or 2007) is the change in the TOP clause if you are doing sorting on the SQL side. Basically, if you drag everything over to 2005 from 2000 it all runs prefectly, Access doesn't notice. BUT the sort order is NOT there.
Basically TOP 100 PERCENT doesn't work for Access, it works for SQL 2005, it appears to sort but bring it into Access and it's in a "random" order.
You need to convert all your TOP 100 PERCENT tp TOP 999999 where 999999 is greater than the number of records in the database and then it behaves.
Have a look at http://msdn2.microsoft.com/en-us/library/bb188204.aspx
Microsoft SQL Server 9.0 Technical Articles
Optimizing Microsoft Office Access Applications Linked to SQL Server
This goes into quite a bit of detail about the changes in SQL 2005.
November 2, 2007 at 9:51 am
Steve,
You addressed your question to Wayne, but I would like to reply. I have been using SQL Server 2005 for over a year. I have about a hundred users who are connecting to SS05 databases for forms and reports all day long. I don't know what connection problems you have read about, but I have not experienced any connections problems in regards to SS05. In fact, other than aweful SSIS experiences, I love SS05 and my Access apps have benefited from the upgrade--reports and all.
November 2, 2007 at 9:56 am
Steve Erbach (11/2/2007)
I wonder if you could answer a question for me: Do you know if Access 2003 connects with SQL Server 2005 properly? I had heard that Access' ability to connect to SS2005 had been quite drastically compromised. If that's the case, then our ability to create Access reports from SS2005 data goes up in smoke.
It absolutely works, as has been otherwise posted. I just wrote a little data entry system for tallying votes for our municipal election in Access 2003 connecting to SQL 2005, and it's fine. (it also drives our web site and two giant projection tv screens in council chambers via ASP!)
I had not noticed the problem with having TOP (100) PERCENT in SQL 2005, but it looks to me like it's more of a SQL problem than Access. I have a pivot view that has an order by and a Top 100 Percent, and if I execute the view code, it is ordered correctly, but if I do a Select * from vwFinalResultsDistrictPivot, the results are not in the same order. Access also provides them in the correct order.
Very curious. I'm going to check this out in SQL 2008 and see if it does the same thing.
-----
[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]
November 2, 2007 at 10:03 am
Wayne -
SQL Server has been advocating that the ORDER BY exist in the outer-most SELECT. So it will ignore any ORDER BY defined in the view. From what I've been reading on here - ORDER BY is going to be ignored/deprecated in CREATE VIEW statements in the upcoming releases.
Not sure that I agree with that, but that's one of the thing I've been seeing pop up.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 2, 2007 at 10:19 am
Matt Miller (11/2/2007)
SQL Server has been advocating that the ORDER BY exist in the outer-most SELECT. So it will ignore any ORDER BY defined in the view. From what I've been reading on here - ORDER BY is going to be ignored/deprecated in CREATE VIEW statements in the upcoming releases.Not sure that I agree with that, but that's one of the thing I've been seeing pop up.
OK, I had to re-read your post a couple of times and look at my code. So when I'm executing the code direct, it works, and that's ok. When querying the view it doesn't work, because that's not the outer-most select. Makes sense.
I can understand them deprecating it, I always considered the Top/Order By in a view to be a bit of a kludge, order should be a presentation layer issue. I don't use it often, but when I was developing all of these ASP pages for this vote tally system, I did not initially know how to order the data grids and it seemed useful. I'm fairly straightened out on ASP now and mildly competent. :Whistling:
-----
[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]
November 2, 2007 at 10:27 am
JJ B you wrote:
For example, on start-up, I have some code that loops through pass-through queries and links to the relevant databases for each query... The code for setting connection strings could use a DSN or go DSN-less.
Do you mind to share some code? Thanks.
Also, I have a unpleasant problem with ADP and SS2000/05. When trying to run behind an Access form (in VBA) a View on the SQL Server I get in some permission troubles - none of the other users can open them even if I created the views using a role ("SomeRole") with all the rights. I tried different qualifier combinations like "dbo.View1", "SomeRole.View1" etc. but it only worked for me, i.e. the "dbo". I think it doesn't happen with stored procedures because different methods are used. Have you had similar troubles?
November 2, 2007 at 10:46 am
I've run into the Order by problem on the VIEWS in SQL2005 and I personally think they should have made it a Compatability setting instead of changing the behavior. It makes sense, but they make sure that everything else was backward compatible except this? WTF. This is the only thing I've ever found that was not backward compatible in 2005.
There are MS Blog entries from the MS developers about some esoteric/purist *this is the way it should have been* logic, but why did they change the default behavior. I'd bet that many people are unaware of this and it will sneak up on them later.
Here's how you work around this if you need a quick fix and you can't rewrite the vendor code that uses the view. BTW - This only happens with top 100 percent in the view. You can try this kludge, put in a top with a very large number for example "select top 214159000 * from xxxxx order by xxx" this works differently than "select top 100 percent * from xxxx order by xxx".
HTH
November 2, 2007 at 10:54 am
Wayne, JJ B, and jfmccabe,
Thank you all for your replies. That is very good news. I can't say where I heard about the problems I mentioned. Must have been hallucinating!
Sincerely,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
November 2, 2007 at 11:15 am
In Access 2003 project connected to SS2000 you can use the visual query builder of Access - for example to give the opportunity to your users to query quickly (without your time waisted) a (test) copy of the production db to answer their insignificant questions, to train etc.
For Access 2003 connected to SS2005 you can't do that - may be with Access 2007 or some patches it's possible (the client version, i.e. Access has to be newer than the SQL Servers?)
Not a big problem but can save you a lot of time...
November 2, 2007 at 12:45 pm
Hi All,
Great article, I've been using the MDB / linked table method in very limited circumstances for a few users for some time now, and it's worked well. I just have a few questions:
1. I just tried creating an Access Data Project for the first time today, and for some reason, the tables in one of my schemas are showing up as belonging to a different schema, so I'm unable to see them. Also, it looks like many of the tables aren't showing up at all. This database has several hundred tables, is there a limit to the number an ADP can display? Is there any way to limit the tables shown in the "tables" section?
2. For some reason, when using the linked tables through an .MDB file or .MDE file, I'm able to use certain functions on my own machine (e.g., some date functions) that don't work on some client machines. Has anyone else run into this? I assumed it was because I had a ton of developer tools installed on my machine that weren't installed on the clients' machines.
Also, for the Mac (and PC too, heck) users out there, when connecting to SQL Server from a 3rd party application, whatever you do, Just Say No to FileMaker Pro!
Cheers,
-m
November 2, 2007 at 1:32 pm
Mcloney -
as to your issues with functions... If your references don't match, your clients will have difficulties running certain functions. In other words - if your MDAC versions are different (down to the support pack), or (if you have non-standard references) if the other DLLs referenced are missing/different versions, you might get "unknown function" , etc... like that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 2, 2007 at 2:21 pm
Iordan Slavov (11/2/2007) wrote:
Do you mind to share some code? Thanks.
Also, I have a unpleasant problem with ADP and SS2000/05. When trying to run behind an Access form (in VBA) a View on the SQL Server I get in some permission troubles
Iordan:
I don't use ADPs and I can't say I've seen your particular problem. Sorry I don't have more help on that score.
As for specifics on auto-connecting pass-through queries to the correct database, I'm happy to share my method. I'm sure there are better ways to do this, but this works for me.
The background is this: The database has an auto-exec macro which calls my fAutoExec() function. The fAutoExec() function calls the function you see below. Thus, the code below runs when the .mdb is opened. It does not seem to have an impact on performance. (Unlike auto-linking tables which seems to affect the speed at which the file opens. For linked tables, I just use the linked table manager or some other code I run manually as-needed to link tables to a different database.)
The code below relies on two settings:
1) pass-through queries have the first 8 characters of their description property set to one of the pre-defined values in the select case statement you see in the function below. For example, main__db might be the first 8 characters of the description property to say that that query should point to the main db (which can be set to point to production or test databases based on the value of the global variables--see next point).
Side benefit: By making yourself view the objects in "Details" view, you might encourage yourself to document your objects!
2) global variables are set to contain the desired connection string. For example, gConnMAIN__DB might be set to point to your main database or a test database. You can use a connection string that contains a DSN or one that does not. There are several websites out there that will help you figure out your connection string.
Hope this helps.
- JJ
*****************************
Function fPassThruConnect() As String
' Comments: Changes the connection string for pass through queries.
' This function is used by both by the AutoExec sub and the
' developer either when something changes about the basic back-end
' database. It is called from AutoExec
' because changing the back-end database is done often - such
' as when the training app needs to be set up.
' This function works by looping through all the querydefs
' and changing the connection property as specified by the
' global variables that begin with gConn... and as specified
' by the text at the beginning of the description of the query.
' If the first eight characters does not match the text looked
' for in the Case Select statement below, nothing is done.
'
' !!BEWARE!! You will get a property error in this function if
' the file contains a pass-through query which does not have any
' description at all.
' Arguments:
' Returns:
' Created: Thursday, December 07, 2000 3:10:41 PM JJ
' Modified:
' --------------------------------------------------------
On Error GoTo fPassThruConnect_ERR
'Declare and initialize variables
Dim qrydef As Variant 'for looping through the querydefs
Dim prpDesc As Property 'needed to be able to read description property
'do the loop
For Each qrydef In CurrentDb.QueryDefs
'Only for pass-through queries do we want to re-set the
'connection property
If qrydef.Type = dbQSQLPassThrough Then 'we have a pass-through query
'On-line help says that you can't access the description property
'without putting in this line of code. So, oh-well, here we go.
Set prpDesc = qrydef.CreateProperty("Description", dbText)
'Use the first eight characters of the query description
'to decide which connection string to assign.
Select Case Left(qrydef.Properties("Description"), 8)
Case "MAIN__DB"
qrydef.Connect = gConnMAIN__DB
Case "STATE_DB"
qrydef.Connect = gConnSTATE_DB
End Select
End If '{If qrydef.Connect <> "" Then }
Next qrydef
Exit_fPassThruConnect:
Exit Function
fPassThruConnect_ERR:
MsgBox "fPassThruConnect: The following error occured: " & Err.Description
Resume Exit_fPassThruConnect
End Function
November 2, 2007 at 6:38 pm
Iordan,
Ah! That may be what I heard about Access 2003 vis-à-vis SQL Server 2005...that the Access visual query builder can't be used. It certainly does save a lot of time. That has been the whole attraction of using Access as a front end for SQL Server all along.
Regards,
Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com
Viewing 15 posts - 46 through 60 (of 71 total)
You must be logged in to reply to this topic. Login to reply