Error 2227 Issue with clipboard in Access 2007

Error 2227: The data on the Clipboard is damaged, so Microsoft Office Access can't paste it.

Had to catch the error and advise the user to close excel first!

This nightmare was caused by an patch to excel 2007.

So put this error message at the bottom of the procedure:
Noting it was in an access 2003 database but the error occurs when running access 2007:

If Err.Number = 2227 And Application.Version = "12.0" Then
MsgBox "There is an issue with an update of excel 2007 and you need to close excel before pasting", vbCritical
Else
MsgBox "Error pasting and updating data" & vbNewLine & Err.Description & vbNewLine & "Error No:" & Err.Number, vbInformation
End If

Re: the Dot Net User group on Wednesday night 18th March 2009

Learnt a few things but went hungry…

What’s the deal that we missed pizza etc
Poor show, sitting there hungry while trying to digest c# stuff…..

News
– There was something about Microsoft Slashing Pricing by 15 to 25% for enterprise server software?
- .Net Code Camp in first weekend in April…

Slide Rocket is cool to do presentations…Can then output to PowerPoint.
Does nice spinning like a cube and allows you to easily select pictures from flicker etc

iPhone
They showed some of the new apple features coming up for the iPod….Maybe that’s why we got no pizza!!!!
Interesting how many apps and developers are into this now, all with apple taking 30% cut of everything!
Wait till the european commission tells apple to cut the monopoly!
There was even a dmf like app for SharePoint that runs on the iPhone!!!!

Saas stuff was though provoking.
There are quite a few things in the cloud and they are getting very compelling as an alternative.
Things like using external providers for bulk emailing, accounting, CRM, Support….
e.g.
Saasu for accounting rather than myob……cheaper and better and web based!
Constant Contact or Campaign Monitor for bulk emailing / newsletters as they have anti-spam agreements you can safely bulk email etc…
Noting the Campaign Monitor is sydney based…..
Zendesk and LivePerson for support

Cross site scripting – making your site more hackerproof
Then a quick one on cross site scripting and how an enterprising guy wrote some javascript he put into his facebook page that wrote a comment in anyone’s page who visited his.
Just a case of adding javascript to do something in a comments area on a web page…think blogs as well etc…
That hack got a good comment about him on thousands of pages very rapidly…..
In .net you can ensure comments cannot have javascript inserted by using html encoding….or antixss.htmlencode …..
Or write a function and add it to your tagmapping in the web.config for all lables and literals…….

6 Design Patterns…
Heavy stuff and kind of do some of them anyway depending on the situation.
For more information suggested first going to Wikipedia…also good for lots of other coding situations and information.
Interestingly the suggest was that there is a book “Head First Design Patterns” code patterns with a blonde with pony tails on the front – obviosuly marketing ot the geek factor…
Even though it is in java, to translate to c# is as simple as 1 line…..
The other book was c# 3.0 design patterns….
Justin King the presneter said he had another book about design patterns but it was so boring he fell asleep on every page.
www.dofactory.com has examples one can download examples from teh books above...


Regards,
Tom Bizannes
http://www.macroview.com.au

Synchronizing lists in SharePoint with Excel

Yes you can do it in both Excel 2003 and 2007

Did you know that you can get an addin for Excel 2007 for two way synchronization and you can write a macro to update a list from SharePoint via VBA code?

Just google "Publishing ad Synchronizing Excel 2007 tables to SharePoint Lists" to find the addin.

As for the Excel 2003 or 2007 vba code, this was cool.

A client wanted to color code a sheet from excel so we wrote code to dynamically create the list, copy it to another worksheet and colour in cells depending on the value in one column...

Just one click and this list always comes out nicely formatted.

Another case where a little coding can make life easier!

For Excel or SharePoint questions just goto:
http://www.macroview.com.au

SharePoint Designer Issues with local - remote screen

If you don't refresh the local and remote views by clicking on the refresh item, it doesn't sort by date although the newer date shows!
Also, getting issures were a file uploaded is still showing as changed because the auther is different...
But I just uploaded it!
So something weird about the PC setting is making this show me as another user.

Startup options for Microsoft Access

You can add command line switches when opening up access via a shortcut or bat file.

To go one step further we had to also use a specific security file.

See below for the bat file script:

@echo off
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" %~dp0MyDatabase.mdb /wrkgrp %~dp0MySystem.mdw
cls
@exit

This opens using access 2003 a database called MyDatabase and a Security file caled MySystem.

What is nice is that everything is relative to where the script lives...

Apparently this opens in shared mode and the other command line options are:

/excl   exclusive access.
/ro     read-only access.   
/user   Starts Access using the specified user name
/pwd    Starts Access using the specified password
/excl   Opens the specified Access database for exclusive access.
     To open the database database for shared access in a multiuser only environment, omit this option.
/x  macro    Starts Access and runs the specified macro.
          Another way to run a macro when you open a database is to use an Access AutoExec macro.                        
/cmd         Specifies that what follows on the command line is the value that will be returned by the Command function.
          This option must be the last option on the project command line.
          You can use a semicolon (;) as an alternative to /cmd.
/nostartup   Starts Access without displaying the startup dialog box
/wrkgrp      Starts Access using the specified  workgroup information file.               database
/runtime     Starts Access in run-time mode.

And these look good to use if you want to schedule compact and repair etc
Although, we put temp tables in separate tables to avoid database bloat and conflicts, so compact and repair isn't required.

/compact [target database]   Compacts and repairs the Access database
/convert [target database]   Converts an Access database in an earlier version
* Specify the source database before using the /convert option.

For any more detail just Google:
"How to use command-line switches in Microsoft Access"

###
Thinking of Migrating Access to Sql Server?
Talk to the guys who know all the tricks....
http://www.macroview.com.au

Sql Server User Group on Tuesday 10th March 2009

Wow, a large crowd and many questions.

The topic was SharePoint for Sql Guys

Was trying to present three things but so many questions we barely got through the first bit.

The three parts were:
What you need to tell your SharePoint Administrator
What you need to do
What tools can help you.

The feedback was quite good with most liking the topic etc

Will now need to add my scripts and presentation

Would recommend you run the reports that monitor the server or use settings to check the fragmentation
See….http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

You are probably better having a quick look at:
http://technet.microsoft.com/en-us/library/cc964305.aspx

sql server reporting services 2008 windows 2008 and sharepoint can all live together

Finally had the time to suss this out.

First you need to know the tricks to get to the hidden sharepoint admin settings and then there's one little config setting that will stop nearly every attempt to get it to work regardless of whether you use SharePoint integrated mode or native mode.

All I can say, is alot of people would strangle whoever created the sql server 2008 reporting services install because this one setting is just ridiculous.

And to think I thought it was IIS7 that was causing the issue!

Was getting errors like:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

and rsInvalidDataSourceReference

Regards,

Tom Bizannes

SharePoint and Sql Server

Sydney, Australia

http://www.macroview.com.au

Reporting Services and SharePoint Integration on Windows 2008 server

Had fun trying this on a windows 2008 box.
Note this also applies for setting up on a vista box....
Everything worked after hitting each bit a few times!
So important to get permissions set, root site collections set properly and IIS7 configured right before installing anything.

So the steps to install in order are:
Set up IIS7 with all the correct settings:

Common HTTP Features:
Static Content
Default Document
HTTP Redirection
Directory Browsing

Application Development:
ASP.Net
ISAPI Extension
ISAPI Filters

Security:
Windows Authentication

Tools:
IIS Metabase Management
IIS 6 WMI Management

Then make sure you set tcp and named pipes in the sql server surface area configuration tool for the remote connections.

Then install service 2 for sql server and the service pack for the service pack if it isn't up to version 9.0.3054

Then run the sharepoint reporting services msi file that integrates them noting this doesn't run on a windows 208 server unless you install a reg hack to run as admin or turn off the uac.

Then you need to go into the reporting services configuration tool to configure and that is where many get mixed up.
Reporting Services needs to use the same pool as sharepoint to integrate.

Everything finally fell into place except the reporting services configuration settings didn't show in the sharepoint admin application page.
Had to go to the _layouts pages instead.

When deploying your reports, the pc with report designer should also be service packed and then you get an extra option regarding sharepoint in your deployment options wording for your target folders etc.

http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx
http://servername:portno/_layouts/ReportServer/ManageTrustedAccounts.aspx
http://servername:portno/_layouts/ReportServer/ReportServerSiteSettings.aspx?Settings=RS


Regards,
Tom Bizannes
Sydney, Australia
Microsoft SQL Server and Microsoft Access Consulting