Dave's Technophorical Times

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

Option Compare Database
Option Explicit

Private Declare PtrSafe Sub keybd_event Lib "user32" ( _
    ByVal bVk As Byte, _
    ByVal bScan As Byte, _
    ByVal dwFlags As Long, _
    ByVal dwExtraInfo As Long)
    Private Const VK_NUMLOCK = &H90
    Private Const KEYEVENTF_KEYUP = &H2
    Declare Function GetKeyState Lib "user32.dll" ( _
    ByVal nVirtKey As Long) As Integer
 
Public Sub NUM_TOGGLE()
    keybd_event VK_NUMLOCK, 1, 0, 0
    keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub
 
Public Sub NUM_On()
    If Not (GetKeyState(vbKeyNumlock) = 1) Then
        keybd_event VK_NUMLOCK, 1, 0, 0
        keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
    End If
End Sub
 
Public Sub NUM_Off()
    If (GetKeyState(vbKeyNumlock) = 1) Then
        keybd_event VK_NUMLOCK, 1, 0, 0
        keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
    End If
End Sub



In Access 2010 I recently got an error when sorting a column or opening a Form that had a sorted column. It was a popup like you would see when you had a missing parameter in a query. It read qryQuery,FieldName.FileUrl and a place to enter the value. It had me stumped so I opened the form in design and removed any sorting. I was then able to open the form OK, but when I clicked on the first column to sort it gave me this error. BTW it was a Split Form with the Datagrid on the bottom and only the Form section on top was editable.

I did a manual Compact and Repair and the then the issue disappeared. Funnily enough this also happened in the compiled ACCDE. So to fix the ACCDE I just did a manual Compact and Repair ACCDB and THEN made the ACCDE. Then it worked fine.

I hope this helps someone else out there one day!



This trick works for a domainless environment AND when you want to connect to SQL Azure tables from you Access Web App.

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 Code and hide the code by delivering a ACCDE or just password protecting the code. The choice is all yours.

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



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

 



PROBLEM:

Some users of an ACCDE file get this error message:

"The database cannot be opened because the VBA project contained in it cannot be read."

 

Here are the details of what I'm doing:

1. I Make ACCDE from MS Access 2010

2. I put ACCDE on the network

3. Users try to load the ACCDE.

4. Some users are successful.

5. Some users get this message:

"The database cannot be opened because the VBA project contained in it cannot be read."

 

All users have the same install procedure:

1. Project Referenced DLLs

2. MS Access 2010 Runtime

 

Notes:

1. The ACCDB file opens and runs properly in Access Development Environment.

2. The ACCDE worked at some point on the user's machine that it now not working.

3. Some of the previous versions now do not work either.

4. We have tried decompiling , compact and repair, and starting a new DB and copying the objects in.

 

SOLUTION:

You have compiled the MS Access application with a version that is higher than the one used by the person trying to open the ACCDE file.

This could be as simple as compiling with MS Office 14.0.7151.5001 and the users getting the error is running 14.0.4760.1000. Onece the user is updated to 14.0.7151.5001 then the applicatioin will work again.

https://support.microsoft.com/en-us/kb/2533794




In MS Access, sometimes you can get a Write Conflict when trying to update a table that is linked to SQL Server. Following is two suggestions on how to resolve the issue.

1. If the SQL Server table has a bit column, then set a default of 0 or 1 and then do not allow NULLS.

2. If the Table has a nvarchar(MAX) or very large field like nvarchar(4000) then try adding a column of datatype "timestamp".



NOTE: The term, "DSN-less connection" is not accurate when using Pervasive.SQL relational access, since a DSN must be defined on the server using the Pervasive ODBC Engine Interface.

In the Example below TDATA System DSN Must exist on the server.

If InStr(1, tblDef.Connect, "ODBC") > 0 Then
 'SQL Pervasive Database
 strSQLConnectionString = GetSQLConnectionString()
 'tblDef.Connect = strSQLConnectionString
 tblDef.Connect = "ODBC;driver={Pervasive ODBC Client Interface};ServerName=10.1.0.16;ServerDSN=TDATA;" & ";TABLE=" & tblDef.Name
 tblDef.RefreshLink
End If

 

More information is here

http://support.pervasive.com/t5/tkb/articleprintpage/tkb-id/Database_KnowledgeBase/article-id/804

Solution Notes

NOTE: The term, "DSN-less connection" is not accurate when using Pervasive.SQL relational access, since a DSN must be defined on the server using the Pervasive ODBC Engine Interface.

The following connection strings can be used to access Pervasive ODBC Engine DSNs programmatically:

1) Setup a DSN on the client machine using the Pervasive ODBC Client Interface. Connection String can be "DSN=myDSN;", assuming the client side DSN is named as myDSN.

2) Without setting up a DSN on the client machine:
Connection String can be "driver={Pervasive ODBC Client Interface};ServerName=myServer;ServerDSN=myDSN;" or
Connection String can be "driver={Pervasive ODBC Client Interface};ServerName=myServer;DBQ=dbName;"

NOTE: Here, myServer is the server name or IP address; myDSN is the server side DSN using the Pervasive ODBC Engine Interface; dbName is the database which is used to define the server side DSN

 



https://support.microsoft.com/en-us/kb/318882

CAUSE
This problem occurs if fields with a bit data type in the SQL Server-based database have been left blank. Microsoft Access interprets blank fields as fields that contain Null values, and the Jet database engine does not release them. As a result, the records remain locked and are not available for deletion.
RESOLUTION
To resolve this problem, do not allow Null values, or establish a default value on the fields with a bit data type. To do so, follow these steps:
  1. NOTE: To perform this procedure, you must have the appropriate permissions to modify database objects (db_ddladmin or db_owner).

  2. Start SQL Server Enterprise Manager, and then locate the server where the database is located.
  3. Expand the Databases folder, double-click the database name, and then click Tables.
  4. In the right pane of SQL Server Manager, right-click the table where the field with a bit data type is located, and then click Design Table.
  5. Under Default Value, type either 0 (zero) or 1.

    NOTE: To disallow Null values, clear the appropriate Allow Nulls check box.


Sub reports are great for showing data from a related table. This works just like a sub form, by setting the master/child linking fields.

What else can you use a sub report to print?

Other data that is not related to a specfic reord in the parent report. The trick is to not set the master/child linking fields. This allows you to print information in the report header or footer that is not "link" to a record.

Sub reports are great for sunmmizing the data in a report.

By basing an sub report's recprd source on the same record source as the parent report, you can show all the totals for each grouping (hiding the detail). You can also create a  totaling query based on the parent report's record source (query)

What if you want to place the report's total in the report header?

Use a sub report that cauclates and displays just the total.   

How do you print several different statistics that are caclulated with different queries?

Use sub reports. It is possible to have a parent report that has no record source, only sub reports.

How to print a header on each page when a sub report spans multiple pages?

Becuse page headers do not print from a sub report you have to use group header. This is done by creating a grouping that will group all record into a single group. You then can set teh group header to repeat.

1. Open the subreport in design view.
2. Open the Sorting and Grouping box.
3. In the Field column of the dialog, enter an expression that won't change, e.g.: =0 and set Group Header to Yes.
4. Right-click the new group header (grey bar on the report), and choose Properties. Set Repeat Section to Yes.

This gives you a group header that appears at the top of the subreport. It will repeat on each new page like the way you would excect the page header for the subreport.

Working with Report Header and Footer sections.

Print order for4 sections:

  • Pager Header - Prints at the top of each page.  This section can NOT be set to grow or shrink. The Page Header prints before the Report Header/Footer. It is possible to turn off the Page Header so it does not print with the Report Header/Footer
  • Report Header - prints once at the beginning. This section can be set to grow or shrink.
  • Group Headers - prints at the begining of each group This section can be set to grow or shrink. It can also be set to Repeat on each new page.
  • Group Footers - prints at the end of each group. This section can be set to grow or shrink.
  • Report Footer - prints once at the end. This section can be set to grow or shrink.
  • Page Footer - prints at the bottom of each page. This section can NOT be set to grow or shrink. The Page Feader prints after the Report Header/Footer. It is possible to turn off the Page Footer so it does not print with the Report Header/Footer

When a report is used as a Sub Report, the header and footer sections work a little different.

Print order for sections:

  • Pager Header - does NOT print. The parent report is responsible for the page header
  • Report Header - prints once at the beginning. This section can be set to grow or shrink.
  • Group Headers - prints at the begining of each group This section can grow or shrink. It can also be set to Repeat on each new page.
  • Group Footers - prints at the end of each group. This section can be set to grow or shrink.
  • Report Footer - pirnts once at the end. This section can be set to grow or shrink.
  • Page Footer - does NOT print. The Parent report is responsible for the page footer.

How to create something lke a  a Page Header in a Sub Report

You can create a dummy header .grouping by setting the group on to  1 (enter the digit 1). You can then set the section to repeat. Now you have a  group header that will print on every page.

Header and Footer Tips

Repeating Group Headers:

In the Group Header's prperties, set the Repeat property to Yes to get the header to print on every page if the group spans multiple pages.

Print Report Header without a Page Header/Footer:

In the Report's properties, set the Page Header property to Not With Rpt Hdr or Not With Rpt Hdr/Ftr depending on your needs.

Print Report Footer without a Page Footer/Footer:

In the Report's properties, set the Page Footer property to Not With Rpt Ftr or Not With Rpt Hdr/Ftr depending on your needs.



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

<<  March 2019  >>
MoTuWeThFrSaSu
25262728123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar

Sign in