Replies

I think this is normal for mail merges. In older versions of Office, you may recall that the permission dialog let you set a time, e.g., 5 minutes, during which you would not be prompted again for access by the same program. Is that still an option in Outlook 2007? Or is it not working?

Theoretically, it is an option; however, selecting 10 minutes has no effect and even if it did, the utility is so slow that it would still be useless.

We still have a Microsoft Outlook forum, but it’s now in the Internet/Connectivity section.

About your question: see View my options and settings in the Trust Center – Outlook – Microsoft Office Online.

Hi Hans:

I’m lost in Woody’s new Lounge setup – need to create a post, don’t see a way to do so, so I’m emailing you directly.

I have been transferred to Office 2007 (not by choice) and need to automate some emails. Rather than re-writing my old Access 2002 VBA code for 2007, I tried using the mailmerge utility, but keep getting the security warning each time Office creates a new email item, despite setting security options to the lowest setting and enable macros on. Any way to disable this annoying security feature, either using vba/automation or???

Please adviseE-mail me.

Thanks!

Thanks, Hans! I knew you would know.

I’ve re-linked the back-end tables using Trusted Connection, which according to the “help” means that SQL Server will use integrated login security and a password isn’t required. However, it only works on my PC, where I can logon to my app user her Access account and then click the “use trusted connection” check box and all the data is available. But when I move the client to her PC and login using her Access account, the same error messages appear. (But I can logon to the client using my login on her PC, and everything works fine.)

I don’t see anything in the Linked Table Manager Options to save a password, but apparently, that isn’t needed except for SQL Server authentication.

I’m almost getting desperate enough to give her my password!

Please help if you can.

Thank you,

There is only one DSN file. It is on the network.

Thanks for the tip, but I’m not using Oracle, but SQL Server.

Thank you for responding.

I would like to make the whole situation as transparent as possible for the user so she doesn’t have to login to SQL Server, just to my Access application.
My Access application is secured and will shut down if the admin user tries to open it. Since none of the folks around here are particularly computer savvy, that is sufficient security. What I’m trying to do is to get the DSN written in such a way that when the Access client is installed on different client PCs and different users log in, SQL server won’t choke and ask for a login, either for authentication or for table/view permissions, but will just use my authentication and permissions from the file DSN and linked tables (which are linked using the same file DSN and ODBC).

Hope this clarifies the situation.

Thank you, Hans.

I just had to dig a little deeper in the thread. The error handler looping through Ne numbers worked once I got rid of the second on error goto statement, which Excel 2003 ignored even though I had set my options to break in class modules. Using this methodology, I did not require the API code.

I turned it into a little function, which works great:

Function fnSetAdobePrinter() As Boolean
Dim strNE As String, blnWrongNe As Boolean, i As Integer
DoCmd.SetWarnings False
On Error GoTo err_fnSetAdobePrinter
Application.ActivePrinter = “Adobe PDF on Ne00:”
If blnWrongNe = -1 Then
blnWrongNe = 0
For i = 1 To 99
If i < 10 Then
strNE = "Ne0"
Else
strNE = "Ne"
End If
Application.ActivePrinter = "Adobe PDF on " & strNE & i & ":"
If Not blnWrongNe Then
fnSetAdobePrinter = -1
Exit Function
End If
blnWrongNe = 0
Next i
End If
err_fnSetAdobePrinter:
blnWrongNe = -1
Err.Clear
Resume Next
End Function

Appreciate your help!

Thank you, but no, I tried that. I can return the printers collection, but it doesn’t give me the pesky NE number.

The syntax is easy:

First, you create a synonym:

USE localdatabasename
GO
CREATE SYNONYM nameit FOR remotedatabasename.dbo.tablename
GO

And then, you can query it:

SELECT * FROM localtable INNER JOIN nameit ON localtable.Keyfield = nameit.Keyfield;

I did a little testing and found out that to access the above from an Access client, you must write a passthrough query. The SQL Server 2005 synonym and any queries based on it are not visible in the ODBC DSN from Access. In SQL Server 2005, the synonyms are visible in the Synonyms folder. In order to save views based on them, you have to right-click the Views folder, select New View, and then select the synonyms from the synonyms tab to drop them into the query grid.

This ran really fast from my Access .mdb client. I was even able to link the pass-through query to a local .mdb table, and it still ran really fast.

Has anyone used this functionality as a basis for an application? I might give it a try . . .

Right. So, in code, you would use IF EXISTS etc. But you can also browse the Synonyms folder.

You only have to create the synonym once. It persists unless you DROP it in the local SQL Server database. (DROP SYNONYM nameit)
It will show up in the Synonyms folder once you have created it, but only after closing and reopening SSMS for some reason.

I think one of the advantages to this technique is that you don’t have to write fully qualified names all the time, just once in the synonym creation. You can use the synonyms in code. Another is that if, say, your systems guy decided to move your target database to a different server, you could just drop the synonym and create another one that included the remote server, and it would be transparent to all your queries. (Of course, your systems guy would never do such a thing . . .)

The syntax for joining tables from different servers is:

CREATE SYNONYM nameit FOR RemoteServerName.DatabaseName.schema_name.Tablename

Hi Mark:

Thank you for responding.

I understand about using passthrough queries and linking back-end tables to the client via ODBC. My question concerns whether performance would be faster if I import some tables from a different (commercial) database into the backend so I can establish PK/FK relationships to my custom tables, or whether I could save some space on the server by linking the back-end tables between two different SQL Server databases.

I understand that I can create synonyms to access remote database tables on SQL Server 2005 and query on them as if they were local tables in the current SQL Server database. However, I don’t see a way to select them using an ODBC DSN from the Access client, as they don’t show up as views. (I don’t see a way to save these queries as views in SSMS, and the Synonyms don’t show up in the DSN table list.) So, I was wondering if distributed queries using OLE DB would work. However, I am unsure if either approach would create a major performance hit, especially via ODBC connections to the client, since PK/FK relationships would not be possible on the tables, just in the query joins.

Do you know how SQL Server processes queries joining tables from different databases? And, do you know if one could write either regular remote Access queries or passthrough queries that use such joined tables that could be used from an Access .mdb client, and if so, how to do so and what the performance hit would be?

Thanks, Wendell.

I have ordered Chipman’s and Harkins & Reid’s books, also Dobson’s Professional SQL Server Development with Access 2000 – I guess I’ll have to wade through all 2500 pages, although Dobson just talks about ADPs, which I understand are not very robust. I’m also trying to buy a DVD of Mary Chipman’s and Armen Stein’s TechEd 2008 session, DAT303 – SQL Server Data Solutions Using Access. What I was really hoping for is an SQL Server 2005 course for Access developers.

It seems to me that there must be a huge niche market for training in this area, considering the popularity of MS Access for RAD and the increasing need to upgrade these apps to SQL Server . . . any entrepreneurs out there?

Thanks, Wendell.

re: “referential integrity in Access gets mapped to triggers in SQL rather then using the inherent DRI feature of SQL Server” may I ask the dangerous “why?” question? And what should I do about it? Is it possible to disable this feature of the upsizing wizard?

I did watch the video of the TechEd panel discussion and thank you for the link. Where can I get more technical information about issues in upsizing from Access 2003 to SQL Server 2005? The books I’ve found talk about earlier versions, and I have searched for a training course or video on upsizing from Access to SQL Server in vain.

Thank you for your help.