Microsoft Access

221K

Solutions

51K

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 am getting the below erro with MS access.
error message not available. Result code
E_UNEXPECTED(0X8000FFFF)
0
Using MS Access Database, I need a list of all Primary Keys and their associated Foreign Keys, from All Tables in an Access Database. Using the query below

SELECT A2.name AS Referenced_Table_Name,
       B2.name AS Referenced_Column_As_FK,
       A1.name AS Referencing_Table_Name,
       B1.name AS Referencing_Column_Name,
S.name AS Constraint_name
FROM  sysforeignkeys fk
INNER JOIN sysobjects A1 ON fk.fkeyid = A1.id
INNER JOIN sysobjects A2 ON fk.rkeyid = A2.id
INNER JOIN syscolumns B1 ON B1.id = A1.id AND B1.colid = fk.fkey
INNER JOIN syscolumns B2 ON B2.id = A2.id AND B2.colid = fk.rkey
INNER JOIN sysobjects S ON fk.constid = S.id
ORDER BY A2.name;

I am getting
Syntax Error (missing operator) in query expression fk.fkeyid = A1.id
INNER JOIN sysobjects A2 ON fk.rkeyid = A2.id
INNER JOIN syscolumns B1 ON B1.id = A1.id AND B1.colid = fk.fkey

-- If the experts have a better query than mine, that lists all Primary Keys and their associated Foreign Keys, from All Tables in an Access Database, that would work as well.

Please help.
0
I am using MS Access 2013 with Windows 8.1.  I am looking for a way to extract a qzip encoded file in VBA rather than "shelling out" to an external program. A little background:  I am receiving and importing an XML file that contains a FedEx/UPS shipping label in .zpl format for a Zebra Printer.  The xml file contains the contents for the label in a Base64-encoded string that is then "gzipped".

I am able to successfully read in the xml file and extract the label contents value.  Then I decode the string, so far so good. Now I need to extract the gzip decoded string. I am able to to do this manually using an external program and it works; the label prints fine. I am hoping to avoid having to shell to an external program.

I have heard of a free .dll called "zlib.dll" that I could create a reference to and call the extract routine in code, but I am having problems find the actual file (all links are dead) and then finding any documentation for actual procedure calls.

Any help would be greatly appreciated.

Thank you,

Ira
0
I would like to create a vba expression to add and remove rows by using a combo box containing a value list of 1-40. For example, if I select 3 from the value list, it will create 3 rows. If I put the list to null then 0 rows or hidden. The row consist of 3 combo boxes, and 5 text boxes. How do I create a vba function for such thing?
0
I am currently developing an employee DB at the company where I'm doing my internship.  One of the functions of this Access database will be, to create a photo book (with a report) of all the employees - from the CEO all the way down. I, therefore, have to traverse the tree in such a way that all top-level executives and their assistants come first and then each department and their teams.  This is what the company structure looks like:

hierarchy of the organisation
And this is how the organizational units are stored in the database:

As stored in Access
I am using the following code, which calls itself recursively to traverse the tree/graph:

Function preorderProcessing(parentID As Integer, level As Integer)

    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim X               As Integer
    Dim colOrgUnits     As Collection

    
    Set MyDB = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set colOrgUnits = New Collection
    
    'get the children of the current organizational unit (parentID)
    'when the function runs for the first time, we are looking for
    'the parentID 0, the CEO, as he has no children.
    
    If parentID = 0 Then
        Set qdf = MyDB.QueryDefs("qyrOrgUnits_SearchParentID_Null")
        'first run, org Unit 4
        DoCmd.RunSQL "DELETE * FROM tblOrgHierarchy"
    Else
        Set qdf = MyDB.QueryDefs("qryOrgUnits_SearchParentID")
        qdf.Parameters("parentID") = parentID
    End If
    
    
 

Open in new window

0
I'm using WinSCP in the scripting mode with an MS Access 2K application.

My goal is to use the shell command that runs a batch file that ftp's a file.


THe MS Access application needs to know when the FTP has completed, so I use a batch file to copy a flag into the directory.
The Access Application then loops, checking every second to see with the flag exists, before it continues...


The problem is that while the batch file works as expected when executed from the command mode, it does not perform as expected when I use the Shell command.
I'm pretty sure the problem is the use of the term "exit" in the WinSCP script, but I don't know what to do to solve the problem.

Here is my batch file... StartFTP.bat (again... batch file creates Finished_FTP_Flag.txt as expected)

DEL "\\<myServer>\Database\XMIT-files\Finished_FTP_Flag.txt"
"C:\Program Files\WinSCP\WinSCP.com" /ini=nul /command "open ftp://<myFTP_Site>/" "put \\<myServer>\database\xmit-files\<myFileName.zip>" "exit"  
COPY "\\<myServer>\Database\DistiTools\FinishedConvertingFlag.txt" "\\<myServer>\Database\XMIT-files\Finished_FTP_Flag.txt"

Here is my MS Access (VBA) command (again... batch file never creates Finished_FTP_Flag.txt
 Call Shell("c:\<MyPath>\StartFTP.bat")

Any suggestions???
0
Need help with creating a specific table for Access form. I will pay for you for time. I cannot explain over here, as it is complicated and easy to misunderstand. I will explain over Skype or gotomeeting through screen sharing if you are interested.
0
I had this question after viewing Trouble Editing an ADO Recordset bound to an Microsoft Access Form.

I'm essentially having the same problem, but i've tried the solution at the link and it doesn't work.  I'm linking to a backend Azure SQL Database.  I'm not sure if the provider is my issue or something else.  I will note that at one point i SWEAR i had this working once i switched to a client side cursor, but now it seems only to work if my SQL has no joins and it must just be on a table (e.g. it doesn't even work directly on a view).  The recordset.isupdateable property is true and i can edit the view directly if I open the link from access, from within SSMS, or if i manipulate the recordset itself manually from VBA.  Just not from a form.

Here's a short version of some of my code on a test form trying to get this to work:

    Dim con As New ADODB.Connection
    Dim com As New ADODB.Command
    Dim rs As New ADODB.Recordset

    con.connectionString = "DSN=<dsnname>"
    con.Open
    com.ActiveConnection = con
    com.CommandType = adCmdText  
    com.CommandText = "SELECT * FROM tbl_ActualHours INNER JOIN tbl_Foreman_Data ON tbl_ActualHours.i_Foreman_Number = tbl_Foreman_Data.i_Foreman_Number"
   
    With rs
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open com
    End With
   
    Set Me.Recordset = rs.Clone
0
I'm currently migrating a Microsoft Access client with a Microsoft Access backend to an Azure SQL backend.  I have a fully working version of the client, but that was before we implemented security with Application Roles.  In order to ensure the users are getting an elevated connection I've been slowly rewriting the client to use ADO recordsets with the SQL in the VBA rather than using access queries tied to linked views/tables and pass-through queries.  This has brought up several questions, but let me go ahead and just ask 2 for now and i can put the rest in another thread as they may not be fully related to this question (plus they're the most important).

Simple question first:
1) I am creating ADO recordsets that select views when forms load to tie them to the Form.recordset property.  I am using a client side cursor (Location) with adOpenDynamic (Type) and adLockOptimistic (LockType).  When I do this the forms are no longer editable.  I've confirmed in VBA that the recordset.iseditable property is true for the SQL statement.  In most cases the SQL statement is as simple as "SELECT * FROM <view name>".  The view is editable in SQL Server Management Studio, and the linked view is editable if I'm using an admin account that I don't need to elevate via application role so I know it's not an issue with joins.  Is there some other curor/recordset setting I'm missing to ensure that when I bind an ADO recordset to a form it remains editable?

2)  I also have a more …
0
Hi

Please see below code, which transfers MS Access query into MS Excel.

DoCmd.TransferSpreadsheet acExport, 10, "QryInt", "C:\abcd\abcd.xlsx", True, "2017abcd"

Everyting is fine but sheet name starts with an underscore. ie. "_2017abcd". How can I name the sheet name just "abcd"?

Thank you
A
0
I have a .Net developer creating a DLL that we are going to Reference in an Access VBA application.  However, whilst we can see the DLL methods (once referenced), he is struggling with a couple of aspects such as passing variables through and how best the code the .DLL so that Intellisense works as expected.

Whenever I have worked with external references in the past, I usually access them like this (rough example):

Dim obj1 As New TSDialler.Dialer
obj1.SignalKey = "ABC123456"
obj1.TelephoneDestination = "0123456789"
obj1.InternalID = "ZYX987"
If obj1.MakeCall = 1 Then
    MsgBox "Call made"
Else
    MsgBox "Call not made.  Error: " & obj1.ReturnMessage
End If

Open in new window


However, he has never worked in VBA and is struggling with the various aspects.  His list of questions is below, but I was wondering if anyone had any links/suggestions for him to look at (more than just answering his questions).

Which kind of methods works?
Do properties work?
Constructors work, what about parameters?
How to do event pattern for a custom class and not UI control?
What pieces (that we use regularly) in C# that does not work with VBA when referenced?
Any article/doc that can be referred that has details for questions like above?


Any help would be greatly appreciated - sorry it's so vague.
0
Hi there we have this customer who is using someone else PC on the network to access all the files are shared from their PC. The PC that is sharing everything has no password and the permission is set to everyone.  The PC accessing all the shared files can access everything but access database files.
 They can access some of the database files but not couple of the database files because on the PC that is sharing them some of  the databases are linked to a database called "Address" so When ever they  open any of the database that is linked to the main "address" database it wont let them use it on the pc that is being shared to.

Is there away to change the directory patch on the PC the files are being shared so the PC that is getting all the files can view this databases and edit them.

Screenshot below of the error message

Screenshot
0
I'm using MS Access 2000

When my customers place an order they specify a manufacturer and a manufacturer part number.
Each line item (manufacturer and Manufacturer Part number therefore has a line item price (quantity * unit price)

I need help writing a query that will only show me the most expensive line item purchase from each customer.

For example, if a customer buys 100 pieces Motorola 2N2222 which cost .10 each the line item cost is $10
if that customer also buys 500 pieces of Texas Instruments SN7400N for .20 each the line item cost is $100
My report should generate a row that looks like:
01234 John Doe Texas Instruments $100
01235 Bill Doe    AMD $50

and so forth

Here is a query that lists all parts purchased.  I need to modify it to only display the largest purchase (one row for each customer)
Can anyone help???

SELECT ManufacturerMaster.ShortName, InvoiceHistory.PartNumber, Max([Price]*[quantity]) AS Amount
FROM InvoiceHistory INNER JOIN ManufacturerMaster ON InvoiceHistory.LineID = ManufacturerMaster.LineID
GROUP BY InvoiceHistory.CustomerNumber, ManufacturerMaster.ShortName, InvoiceHistory.PartNumber
HAVING (((Max([Price]*[quantity]))>0));
0
I am totally new to learning vba and access. If someone could teach me that would be appreciated. I have a command button and text boxes. Basically, I want to enter a value in the text box, and populates the rest of the text boxes related to that value.
0
Lookup Error when search value is not foundDrop Down LookupHi,

  In my  ACCESS database, I have a table where I store company names and passwords.
 and form where I have two records - "AAA" and "CBS" . When searching for the record, if I enter non-matching value, then I get an error.
  Getting an error is ok, but it does not have what I call "graceful exit".
  For example, if I am typing "ab" and press [enter], I get an error message (as seen in the screenshot).
  What kind of visual basic code can I use to provide a graceful exit?

Thanks.
0
How do I Import data that has more than 65535 rows of data?  The follwoing code below is what I use to import data from excel to an access table , the problem I'm having is that I have 159000 rows of data and I'm being limited to import only 65535 rows in access.  Is there way I can import the entire data?

Sub ImportEA()
    Dim acc As New Access.Application
    acc.OpenCurrentDatabase "X:\Procurement\TTE\SAMS_v1.0.accdb"
    acc.CurrentDb.Execute "Delete * from tbl_EA"
    acc.DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tbl_EA", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:="EA$B:S"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing
End Sub
0
Hello, is there an easy way to have a query do a running sum on a field?

Table1
Value      Account_No
1              44-5056450
1              JD-51675265
1              0155550006

I wrote the Dsum like this:
AutoValue: Dsum("Value"."Table1","[Account_No] ="& [Account_No])

and the error returns "Data Type mismatch in criteria expression."

Would like to see:
Table1
Value      Account_No
1              44-5056450
2              JD-51675265
3              0155550006

Just trying to get a field to do an Auto Increment.

Any suggestions?

Thanks, Kevin


In the DSum funtion, I tried
0
I need to do TCP/UDP socket communication with a Linux box in Microsoft Access 365 or 2016 or some modern version.  I have to upgrade an old MS Access app that used wsock32.dll, but I can't get that to register on windows 10.  What is the best way to do socket communication with VBA on Windows 10?
0
I have an MS Access 2016 Database linked to a SharePoint list , users will many times work on the access database offline thus catch changes locally, when online again, user has to manually click reconnect to tables as showing in picture 1picture 1: reconnect tables :

then manually click the synchronize ribbon in picture 2picture 2: synchronize:

I am thinking use VBA to automate this two manual steps, for instance, auto-connect tables and sync database on open, or similar solutions, i thought this would be straightforward because many users need this as Access is a good offline front-end solution for server database, however I have not got any useful information on internet yet.
0
I had this question after viewing Out of Memory Error when trying to compile MS Access 2013.

I have workstations running windows 10 x64 office 2016 x32. most of my workstations have 32gb of ram installed on them. i have a front end db which is at 100mb and a backed db at 600mb. i get an out of memory error periodically when trying to import forms in the the front end and then run a compiler on the db to resolve bugs and issues. i have tried doing a decompile, compact and repair, then recompile and get the out of memory error. what steps can i make to resolve this issue?
0
Dear All,

I am on Access 2016 and I would like to run some custom code when the user clicks on Shift and P (holds down Shift and then press P). I cannot use AutoKeys as +P does not work and Access tells me to use SendKeys, so how can I do it (SendKeys does not work in this case, as it is only sending the keystrokes, but not instructing Access how to respond to them).

Regards,
George
0
I need to use a datasheet subform's .filter property to generate filtered reports and other database actions.  However when I clear the filter using the attached code two things happen:
1) The actual subform is displayed with the filter cleared.
2) The subform's .filter property string still has the old value.

The same scenario occurs if I use the Ribbon icon to Toggle the filter and still the .filter string is not cleared.

 Snapshot showing filter is not cleared
I can clear the field in the Immediate Window just fine, just not with code.

Manipulating the datasource with programatically generated SQL is impractical.

Any ideas?
0
Hi, I'm trying to get an update query to work!  I have 2 tables with the destination table that has a date field (datetime)   (on an MSSQL server) and  the sourse is an Excel file I linked to in Access.

In my Query:

Field: Start_date
Table:  Monthly_rep
Update To: [Excel_rep].[start_date2]

the update To:  I tried to put in CAST , CONVERT  etc keep getting error!  

CAST([Excel_rep].[start_date2] as DATE)
CAST([Excel_rep].[start_date2] as DATETIME)
Cdate([Excel_rep].[start_date2])

ETC.

Thanks

Pete
0
Previous Day Closing as Opening for the next day. A simple query or report with a cutoffdate to get current balance. Access Database

Below is my table and expected result for 3 day mix transaction:

TableInitial
Tdate       Trxn           Amt
-----------------------------------------
Day1        Initial           100

TblCStock
Tdate       Trxn         Amt
------------------------------------------
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10


 
Qry/RptTblCStock
Addl row to be added below each nextday 1st  transaction date.
Legend:

1Day =
Result = Initial - Issue + Receive

2Day = Result - Issue + Receive
and next day

Tdate       Trxn         Amt
1Day        Initial        100
1Day        Issue          50
1Day        Issue          10
1Day        Issue           5
1Day        Recieved    40
1Day        Recieved    50
2Day     Result            125
2Day        Issue           10
2Day        Issue           10
2Day        Received    80
3Day     Result           185
3Day        Issue           20
3Day        Issue           20
3Day        Received    10
3Day        Received    10
4Day     Result            165

Tnx in …
0
Getting an error message: 'Microsoft Outlook - Sorry, something went wrong.  You may want to try again'

This only appears when sending to one specific client from MS Access, all other client emails work fine.

Any ideas how I could fix this?
0

Microsoft Access

221K

Solutions

51K

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.