Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

I'm attempting to convert a query from MS Access to SQL Server and I'm having a lot of trouble.  My command of T-SQL is moderate at best.  This is the query from Access:

TRANSFORM Sum(AR_Records3.AmtS) AS SumOfAmtS
SELECT AR_Records3.CoCd,
       T_ComResFlag.ComRes,
       AR_Records3.Customer,
       AR_Records3.CustName,
       AR_Records3.CGrp,
       Sum(AR_Records3.AmtS) AS [Total AR]
FROM AR_Records3 LEFT JOIN T_AgingBuckets ON AR_Records3.DPD = T_AgingBuckets.DaysPastDue
        INNER JOIN T_ComResFlag ON AR_Records3.SDst = T_ComResFlag.SDst
WHERE T_ComResFlag.ComRes='Com' Or T_ComResFlag.ComRes='Res'
GROUP BY AR_Records3.CoCd,
         T_ComResFlag.ComRes,
         AR_Records3.Customer,
         AR_Records3.CustName,
         AR_Records3.CGrp
PIVOT IIf(Ar_Records3!DPD>365,'6-366 and Over',IIf(Ar_Records3!DPD<1,'0-Current',[AgeBuck]));

How can I write this for SQL Server?
0
Hi

I would like to create a pdf editable form with specific information about products for QA checking so these can be ticked off / completed on the PDF.

This could be from a report or vba code to populate a new pdf or a standard/template with the product info / fields.

Ideally I would like to have text fields, drop down selections, date field and a table with a tickbox in the last column.

Has anyone found a way to do this.

Thanks.
0
We are using Office 365 and have setup a SharePoint site.  For some reason, SharePoint stopped allowing me to edit the main page.  I can go into edit mode and I can edit the first header bar, but the three columns are not editable in the GUI.  I can click on the column and select edit source button at the top of the page and edit it that way, but I am not good with HTML and want to use the GUI interface.

I wouldn't be surprised if one of the owners of the page did not add a carrot to the end of their html code.
0
Error MessageHello,

I am having an error when I try to import data in a spreadsheet to an Access table using the following VBA code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempTable", strInputFileName, True, "Output$"

The error message that I get is:
"Access was unable to append all the data to the table. The contents of fields in 0 record(s) were deleted and 0 record(s) were lost due to key violations"

Attached is a screenshot of the error message.

I am importing to a temporary Access table, and then running an append query to transfer the contents in the temp table to the final destination table in Access. The contents of the temp table are cleared after this second step. The error message is related to the first step (Excel to temp table) only.

If I choose to proceed anyway on the error message, the data transfers without any issues. Interestingly, I only get this error sometimes.

I looked at data types and there are no issues. I am not using any autonumber fields in the destination table

Any help will be greatly appreciated.

-Greg
0
Does anyone have an Access DB that will download stock quotes? I have been using one for many years but it stopped working recently. When I do the download, I receive "Error #9: Subscript out of range". I receive the identical error using back-up copies of the DB on other computers.
  If someone has the basic download part, I can adapt it for my purposes. Or, if someone can suggest a solution to the problem I have encountered, that might work, also.
0
I have many folders I can create via a "on click" cmd that creates folders of "peoples names". Inside each folder are 20 images with the same name. (but different image) EG each folder has files called
1.jpg
1 (1).jpg
1 (2).jpg
1 (3).jpg
 Is there away of displaying the twenty images based on the user ID or name I select? Images are all located in a s:\data drive.
       s:\data\mark
       s:\data\bill
       s:\data\sally ....... etc
Folders.JPG
0
I have at present
Private Sub txtPictureLocation8_Click()
  Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
 
fDialog.Title = "Select a folder"
fDialog.InitialFileName = "C:\"
 
If fDialog.Show = -1 Then
  Debug.Print fDialog.SelectedItems(1)
End If
End Sub

it errors on Set fDialog . can anyone see my mistake?
0
Hello All

I have to export data from an Access table to an Excel spreadsheet.  I did a simple DoCmd.TransferSpreadsheet acExport but now the user wants some formatting.

When I do the exporting now it exports just fine, but the columns are not wide enough in some locations.  Personally I would think that he could simply doubleclick to widen them but he doesn't want the bother.

I remember I did this once before a slightly different way where I set up an excel spreadsheet like a template and just filled in a range (Like A1:A15).  The problem with this is that the amount of rows in this spreadsheet could fluctuate between 1 and around 50.

Additionally He would like a total produced and some text to show up under the individual lines.

Ideally by pressing a button in the Access database it would create the xls file completely formatted.

Is any of this possible?
0
i had a slow running access application which had a backend  of sql. so my manager suggested i copy all the records to a local table in access and then work with the table.

Private Sub Form_Load()
Call Copy
End Sub

Sub Copy()
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM WorkTable"
Me.Refresh
DoCmd.RunSQL "SELECT [dbo_Load Table].* INTO LoadTable FROM [dbo_Load Table];"
DoCmd.SetWarnings True
End Sub

Open in new window



getting the error on the simple copy, i dont have the local loadtable opened. so is  there a way i can just unlock the  table before starting the load?
0
Experts,  with the rise of ransomeware, we have all become more concerned about the accessibility of data to individual user accounts.  I have been discussing this issue with my programmers and have a question relevant to our Access code as well as other code we have developed in Visual Studio...

It seems to me that a reasonable protection of file data would be to use a separate, explicit credential to files and directories that contain application data, rather than using the credential authentication of the application user.  This method would reduce the "attack surface" of shared data in the event that an individual user's workstation became infected.

Does anyone have some insight how to do this effectively without creating a connection to the data share that was discoverable by a rogue app grammatically?  For instance, you can use the command line "net use" command to create a connection to a share using a credential other than your own, but this connection becomes easily visible to any application.
0
Haven't been here in a while.

I am trying to setup the highlight selected record in a continuous form.  Each record has a [delete] button and a [Appt_ID] box.

There is a txtActiveRecord box in the header.  The Delete_OnClick event sets "Me.txtActiveRecord = Me.txtAppt_ID" as the first item in the procedure.  CF then kicks in on "expression is: [txtAppt_ID]=[txtActiveRecord]"  and sets one of the controls to a particular colour. The rest of the On_Click event asks the user "are you sure" etc, and if clicked "No", it all returns to normal.

This works perfectly, EXCEPT the CF doesn't kick in until after the procedure is completed.  i.e. too late.  

However, when I step through the event line by line, the CF kicks in right on cue.  i.e. a the "Me.txtActiveRecord = Me.txtAppt_ID" line.

Where have I gone wrong?

Cheers,
Brodie
0
Good morning.

We have a database and it is run in Access 2000.

On a report with a GIF that has a transparent background as part of the time. The image control for this gif backcontrol is set to Transparent.

One some machines (setup in the same way) the gif is transparent and others it isn't. I have tried changing it to normal as well just in case it was counter acting the transparency.

Any ideas?
0
Hi,
Would very much appreciate people's input here as I am struggling to solve this and it is causing our users a lot of headaches. I am at my wit's end.

I have an ACCDB 2016 Access database hosted on a single 2012 R2 server, split SQL back end, MS Office 2016. Each user connects to the server via remote desktop and a login script copies the ACCDB from a central location to their local documents which is where they access it.

The users are randomly getting corruption on the database. The errors and behaviour include:
  • Error messages about can't find macro, can't find VBA code when clicking on something (as if the macro and VBA coding have vanished underneath)
  • Error message Cannot open database '', expression may not result in name of a macro or event procedure, detected database in an inconsistent state, cannot be opened because VBA project cannot be read, unrecognized database format etc
  • User using the database leaves it idle for a while, comes back, can't click anything (one or more of the errors above), then crashes
  • User using the database and the above happens as they are actively using it (although the idle one above is more common than this one)

I have tried the following without success:
  • Create new blank database, import everything into it (this seemed to work well for a while and then the corruption started again)
  • Decompile / recompile / compact/repair
  • Removing references, adding references
  • Creating ACCDE
  • Repairing Office
0
Hi,

I am still using mscomctl.ocx for controls like treeview, listview etc. But that one can't be too modern, or? Many times I have tried to found a more modern replacement, without success. Is there anybody? Has Microsoft got a replacement?

I also wonder, for new applications, is Mscomctl.ocx the common choice?

Thanks in advance

BAnders

PS I am planing to move from office 2010 to 365. Shall still use mscomctl?????
0
i have a query BalTanktoFabrication which shows the balance tanks to be fabricated.
now i want to calculate the manhours, so i have created a Form TankMHOurCal here i configure the tank specs and calculate the required manhours for each tank.
now further on Form TankMHourcal i have [#ofCrew] [Crew Hrs] and [Bal Hours] as a row and in the above of the form i have Total shift hours which no of employee multiplied by shift hours. for example if the shift hours is 11 and no of employees is 100 than 11x100=1100
my question, i want a text box on form TankMHourCal, which makes running sum of [Crew Hrs] until it reaches 1100 or [total shift hours]. after that it should start making the running sum again from new.
fab-prog-test.accdb
0
Hi
I havean ACCES DB with Delphi. I open the Db from a  menuline open DB. The I have a menuline to close de DB. So I can use different DB for each year.
When I run my software from delphi (execute) it run properly but when i close the Database and without to return to Delphi, if I trie to open it again, I have nothing in my Different db grid. So it seems that it is not open but I haven't any error mesage just blank dbgrid an d DBtext component.
Any idee? Beacause if I close le DataBase and reopen it again it schould work.
Regards
Claude
0
In my MS Access 2016 database, one of the fields is LONG-TEXT type.  I use this field for several paragraphs written information, some of which need BOLD or ITALICS.

How do I Export this information into Microsoft Word, and still Retain the BOLD / ITALICS formatting ?
0
I have several hundred fillable PDFs that I am trying to complete programatically in an Access database.

I have cobbled together something that will enter the required data (this is vastly cut down to highlight the issue)

Public Sub FillForm (strPhone, strAddress, strUserName, strEmail as String)
Dim acroApp As acroApp
Dim PDDoc As AcroPDDoc
Set acroApp = New acroApp
Set PDDoc = New AcroPDDoc
Set avdoc = CreateObject("AcroExch.AVDoc")
filenm = "L:\Sandbox.pdf"
If avdoc.Open(filenm, "") Then
    Set PDDoc = avdoc.GetPDDoc()
    Set pdfform = CreateObject("AFormAut.App")
    pdfform.Fields("Daytime telephone").Value = strPhone
    pdfform.Fields("Email address").Value =strEmail
    pdfform.Fields("Correspondence").Value = strAddress
    pdfform.Fields("undefined_148").Value = strUserName
    PDDoc.Save 1, filenm
End If
End Sub

This worked fine on the pdf I was using to test but it seems that although all the forms look the same the number of undefined fields differs so what would be "undefined_148" in one is "undefined_207" in another or "undefined_122" in another etc.
It is always the next field after "Correspondence" though so what I need is some way of moving one field on from the "correspondence"  or finding the tab index of "Correspondence" and then referring to whatever the next field is called by its tab index.

Any help would be greatly appreciated.

Thank you
Richard
0
Hello

Can anyone tell me why the following is returning 3070

"?" not recognised as valid field name or expression.

rs.FindFirst strFind
   Dim nodX As MSComctlLib.Node

   Dim strBook As String
    Do While Not rs.NoMatch
      Set nodX = tv.Nodes.Add(, , , rs!Stockcode)
      nodX.Bold = True
      strBook = rs.Bookmark
      addChildren tv, nodX, rs, rs!Stockcode
      rs.Bookmark = strBook
   rs.FindNext strBook
   Loop

Open in new window

0
We have a legacy application written in ms access, backend is sql server running in 2000 compatibility mode. The customer wants to upgrade to sql server 2016. We've restored the database to a 2016 instance which essentially breaks the app. The data layer is DAO via linked tables thru a System DSN. Questions really are what are our options here in regards to the database (steps to upgrade) and or application (rewrite to use ADO?)
0
i am trying t o use this function in 64bit but it crashes at the copymemory line,Any fixes?

Private Function IsShellVersion(ByVal version As Integer) As Boolean

  'returns True if the Shell version
  '(shell32.dll) is equal or later than
  'the value passed as 'version'
   Dim nBufferSize As Long
   Dim nUnused As Long
   Dim lpBuffer As Long
   Dim nVerMajor As Integer
   Dim bBuffer() As Byte
   
   Const sDLLFile As String = "shell32.dll"
   
   nBufferSize = GetFileVersionInfoSize(sDLLFile, nUnused)
   
   If nBufferSize > 0 Then
   
      ReDim bBuffer(nBufferSize - 1) As Byte
   
      Call GetFileVersionInfo(sDLLFile, 0&, nBufferSize, bBuffer(0))
   
      If VerQueryValue(bBuffer(0), "\", lpBuffer, nUnused) = 1 Then
         
         CopyMemory nVerMajor, ByVal lpBuffer + 10, 2  '*** see below for explanation
       
         IsShellVersion = nVerMajor >= version
     
      End If  'VerQueryValue
   
   End If  'nBufferSize
 
End Function

thanks
0
I'm trying to use Mail merge to create a 3-level numbered list from data in an Access table. The numbered list works at levels 1 & 2, but loses its indent at the 1st occurrence of a level 3 record. See screenshots of the Access data source, the merged fields in the Word doc (.docx), and the incorrect output after merging into a new document.
Aource data (Access table) Merge fields in Word doc  Word doc after merging
I tried the 3-level merging with .docx & .doc, and the .doc document worked temporarily. I also tried the merge with MDB and accdb source files. It worked for awhile with the mdb/doc combination, but reverted to the Level 3 error after 1 or 2 tries.

See my setup panel in the below screenshot.Milt-tiered numbered list setup panel
0
Hi all,,,

How can I copy Saved Export/Import Procedures from one DB to Another.

Thanks
0
Hello,

I am really new to access, hopefully you can guide me over to what can I use when as soon as an employee table is made inactive the field Active/Inactive or leave date is entered in the leave date field the position needs to become available in the Fills screen table and its fields update  as well, also it needs to be available in a 3rd table recruits.
I am not sure if do a query for that using Append or Update, or using a Macro or link the tables, which one will be more accurate to use to be able to work? these 3 tables also have 3 forms and 3 sub-forms.
0
Hello.  Using A2013 for front and back end with linked tables.  I have a continuous form that is being used as a diary for the users.  After awhile, the diaries are rather plentiful.  There is a date field (short date) when the diary is completed.  Otherwise, the field is empty.   What is looking to be done is have a button and when clicked, the diaries with completion dates will hide or be filtered out.  The same button is clicked and all the diaries come back (a toggling back and forth - but not to use toggle button).  It has to be so that all the diaries can be seen when needed.  The continuous form's recordsource is the table (actually the query builder was used so the due dates are descending) and the diary is added on the form and into the table.  This full A2013 app is to be as simple for the user as possible.  Diary form is only one form in the full app.

I have tried the following:
DoCmd.ApplyFilter = ...  (this gave error about filter or where not set - that thru me off and not sure what it meant)
me.filter = ...  followed by me.filter = true  (even tried all these in an IF statement - but no go)
Even tried to use the Sort & Filter menu on the ribbon but realized that the ribbon is collapsed on most all machines.  

Can someone point me in the right direction in using the button to filter completed/not completed?

Thanks..... John
0

Microsoft Access

221K

Solutions

52K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.