Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

The best solution is obviously to use Windows security.

If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until Access is closed:

  1. copy the connect string of one of your tables
  2. create a passthru queries "ptqConnect" and enter any fast SQL statement in it, like SELECT 1
  3. paste the connect string of into the PTQ Connect property, and make sure you add the PWD=something; in it.
  4. in the startup procedure of your app make sure you call that PTQ. Something like DCount("*", "ptqConnect") will do.

That's it. Since Access remembers opened connections until you close it, even if you close the db, your other tables will now open without any fuss, even if no password is stored in the linked tables Connect string.
If you don't want to expose the connection string that includes the PWD, you could as well initiate a connection from VBA and hide the code by delivering a MDE or just password protecting the code.

 

From here: http://stackoverflow.com/questions/9093266/save-password-for-odbc-connection-to-ms-sql-server-from-ms-access-2007



Private Sub cmdRefreshAllRecords_Click()
    Dim strGuidID As String
    Dim rst As DAO.Recordset
       
    ' This is NOT how to get the GUID
    ' The result will be "????????"
    strGuidID = Me.ID
   
    ' This will get the GUID. StringFromGUID is a built in MS Access function
    strGuidID = StringFromGUID(Me.ID)
   
    ' You can also get the GUID from the actual Records Field
    strGuidID = Me.Recordset.Fields("ID")
   
    Me.Requery
   
    Set rst = Me.RecordsetClone
   
    If rst.RecordCount > 0 Then
        ' Here we must convert the ID field using the StringFromGUID field.
        ' ID is the name of the field in the Recordset/Underlaying Form
        rst.FindFirst "StringFromGUID(ID) = " & strGuidID
        Me.Bookmark = rst.Bookmark
    End If
   
    Set rst = Nothing
   
End Sub



CREATE TABLE #counts ( table_name varchar(255), row_count int )

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC



QUICK FIX

http://blogs.technet.com/b/the_microsoft_access_support_team_blog/archive/2016/03/09/you-may-encounter-errors-with-your-accde-mde-files-and-or-wizards-after-installing-the-march-update-for-office-2010-kb3085515.aspx

 



I deployed a SharePoint feature that has an Event Receiver and ran into this error when trying to update the Today Column during a nightly batch run. The Error occured on random Document Libray items. So I did some research and found out that I needed to update the Event Receiver to run as Synchonous when Updating items. This solved the problem! By default Event Receivers run Asynchrounsly, which is not always ideal, especially in this situation.

To fix this problem just update the Element.xml file for the Event Receiver in question for the event type (Updating) you want to run as Synchronous as follows. Then redeploy. Be sure to Deactivate and Activate the site feature once it has been redeployed since it might not be used everywhere.

<Synchronization>Synchronous</Synchronization> under Elements -> Receivers -> Receiver tag

Also in my nightly batch job I used the following code to disable Events when Updating... The Using Code is further down the page.

//Disables Events when updating
using (var scope = new DisabledItemEventsScope())
{
SPList list = oWebsite.Lists["Code Rules"];
//This must be switched on, otherwise the Update method will throw an exception.
collWebsite[i].AllowUnsafeUpdates = true;

for (int k = list.Items.Count - 1; k >= 0; k--)
{
SPListItem item = list.Items[k];
//This only Works if the List Item was manually Updated before using a column called Today.
item.Properties["Today"] = DateTime.Now.Date.ToString(CultureInfo.InvariantCulture);

//SystemUpdate Updates without changing the Modified Time OR Modified By and DOES NOT make a New Version!
item.SystemUpdate(false);
}
collWebsite[i].AllowUnsafeUpdates = false;
}

        #region DisabledItemEventsScope
        /// <summary>
        /// Disabled item events scope
        /// </summary>
        /// <see cref="https://adrianhenke.wordpress.com/2010/01/29/disable-item-events-firing-during-item-update/"/>
        class DisabledItemEventsScope : SPItemEventReceiver, IDisposable
        {
            bool oldValue;

            public DisabledItemEventsScope()
            {
                this.oldValue = base.EventFiringEnabled;
                base.EventFiringEnabled = false;
            }

            #region IDisposable Members

            public void Dispose()
            {
                base.EventFiringEnabled = oldValue;
            }

            #endregion
        }
        #endregion DisabledItemEventsScope

Good Luck!



  1. Type in secpol.msc /s
  2. Select "Local Policies" in MSC snap in
  3. Select "User Rights Assignment"
  4. Right click on "Log on as batch job" and select Properties
  5. Click "Add User or Group", and include the relevant user.

 

I'm posting this as some idiot at a stupid company (you know who you are!!) changed a Global Group policy and it broke the permissions for the Service Account used to run Task Manager Jobs.

Good Luck!



1. Run regedit

2. Go to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common (repeat for muliple versions of MS Office)

3. Create a KEY called "Security" here by right clicking on the Common key and selecting New->Key and typing "Security"

4. Create a DWORD in the Security key by right clicking on Security, selecting New->DWORD and type in "DisableHyperlinkWarning"

5. Change the value of this DWORD to 1



Someone created a VM for me recently and everyime I launched IE to get to our SharePoint site internally I was being prompted to login. So I changed the IE Setting to use my credentials to log in automatically. That was OK for the time I was on the VM. BUT, when I went back in it asked me again and my settings in IE had been reverted.

This was so annoying that I Googled it and found out that it was the IE Enhanced Security Configuration that was causing this problem. So I switched it off and not it doesn't prompt me any more :-)



You write some LINQ code to update your SQL tables like this below BUT it does not throw an exception and it does not Update the tables!

using (var db=new SomeDatabaseContext())
{
     db.SomeTable
       .Where(x=>ls.Contains(x.friendid))
       .ToList()
       .ForEach(a=>a.status=true);

     db.SubmitChanges();
}

The reason why is because your Table does not have a Primary Key! You can update your DBML designer to have that Primary Key and you don't need to touch the actual SQL Server tables for this to work!

Best of Luck.



WITH  q AS
        (
        SELECT TOP 1 *
        FROM    mytable
        /* You may want to add ORDER BY here */
        )
DELETE
FROM    q

Note that

DELETE TOP (1)
FROM   mytable

will also work, but, as stated in the documentation:

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Therefore, it's better to use WITH and an ORDER BY clause, which will let you specify more exactly which row you consider to be the first.



The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca

Calendar

<<  May 2016  >>
MoTuWeThFrSaSu
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

View posts in large calendar

Sign in