[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Access

220K

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

Hi Experts,

I'm looking to create a VBA function that will accept a file name as a string parameter, then rename a file as follows.
if parameter is "Skilled_Nursing_Visit_Note" then will look at given folder for file starting with that name for example "Skilled_Nursing_Visit_Note_2018-Nov-26_1901.csv"
and rename that file to just "Skilled_Nursing_Visit_Note.csv", in other words, remove the date/time and sequence timestamp from that file name.

Thanks
0
Update table in database from another database.

I have two databases, DB1(SQL Database) and DB1 (access database). I opened an empty access database and linked to DB1 and DB2, now I want to update table in DB2 using data from DB1. Can someone please help me with the query?

DB1                                                      DB2
TABLE1                                                TABLE1                        TABLE2
ID_field                                                id2_field                      id2_FIELD
property1                                           property1                    ID_field
property2                                           property2


Here the challenge is the receiving table (DB2 TABLE1) does not have the same Id_key (ID_filed)  so I have to use TABLE2.
Any help would be appreciated.
Thank you.
0
Hi Experts,
I need to link an Excel file to my Access application, however realized that most of the fields are being defined as short text (255) after being linked thru the wizard.
Now this is causing trouble as fields with longer text are being truncated, and I don't see a way of being able to change these mappings.
Any (easy) suggestion?
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
ACCESS 2010
EXCEL 2010

I have  an excel formula:

that i need to put into a Access case select statement

=IF(OR(T4>=-250,S4>8%),"Update Quote",IF(AND(T4<-1000,S4<4%),"FVP Approval","RSVP Approval"))

T = Estimated Revenue Impact (Based on 2018 Usage)  (money)  
S= 2019 Escalated Price Impact(percentage)

Thanks
fordraiders
0
I have a client with an Access application. The system has a front-end linked to a back-end running compatibility mdb files. There are approximately  10 users connected to the application using various versions of Microsoft Office.  The system is experiencing frequent issues which appears to be corruption in the database. When this happens they are receiving  “Disk I/O error trying to read” and sometimes “Unrecognized database format J:\Purchasing\Inventory Control\MICBE_NA_1.0.2.mdb”  error messages. Originally it appeared that these issues occurred when a single table was being read "Item_Master". However yesterday this happened when a user clicked a bound checkbox having now connection to the Item_Master table.  One time the primary key was destroyed in their PurchaseOrders table which we were able to fix after identifying and fixing duplicate key records.

We have continually executed compact&repair procedures and also decompliles.

I know we need to upgrade the back-end to SQL Server but my client is reluctant to do it because they like to connect directly to tables and queries for data entry and maintenance.

If they upgrade to the newer "accdb", is that likely to solve the corruption issue? Are there any procedures we should try to see if we can identify the cause and solution.

Thank you very much!!
1
Hi,
I have an MS Access database (2007-2013 format, .accdb) split into front & back end. It was developed on a Windows 10 PC and uses tabbed subforms. The live environment is  also on a Windows 10 PC with just 2 users, the second user on a separate PC with their own FE copied onto that PC when starting the database.
It seems to run fine on the development PC but on the live environment they are coming across the Error 3048 "too many databases" problem.
I've done a lot of research on this error and I understand the difficulties in pinning it down but I've made the following changes:
1) All DAO datasets are closed and the associated "set" commands are all cleared to "nothing"
2) I've replaced all "DLookUp" with Allen Browne's "ELookUp" routine
3) I've tried late binding on some of the tabs so that all recordsets are not populated when starting
The problem is still happening after making these changes & from what I've read it looks like I need to re-design the application but this is a major decision for the users to take & I'm wondering if there are any other things I could try as a stop-gap. I'm considering the following:
1) Forms that use queries as the RecordSource - would there be less overhead if I were to use SQL statements rather than queries?
2) Would a SQL back-end running on the local PC solve this problem?
I'd be grateful for any suggestions please.
0
I have a Microsoft Access 2003 database with security enabled with a security.mdw file. I would like to remove all security, delete the mdw file and covert to 2016 Access if possible.

There is a similar question that was posted here a while ago but the link to the answer is broken.

any help would be much appreciated.

Thanks
Bob
0
Hi Experts,

I have the following query listing all records with missing data (in those fields listed)
SELECT Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, Skilled_Nursing_Visit_Note.Visit_Date, Skilled_Nursing_Visit_Note.SNV_ID
FROM Skilled_Nursing_Visit_Note
WHERE (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90 And (Skilled_Nursing_Visit_Note.Visit_Date) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Client_Last_Name) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Client_First_Name) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Date_Of_Birth) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Shift_From_Hour) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Shift_To_Hour) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND 

Open in new window

0
Hi,

I have an access application coded in MS Access 2010 and I wanted to know if there is an upward compatibility with MS Access 2019.

Also,

Is MS Access can be used as a stand alone application on a PC vs. a subscription License based application ONLY in that case I have to deal with unintended consequences due to updates pushed by Microsoft.

Guidance will be appreciated.
0
I have just completed a database project, only to be told that they want it to run on a Mac. I know I should've asked this up front, but assumed that as they didn't mention it, it was for a PC.

Being a charity, they are reluctant to use Parallels because of the cost of 10 licences.

Does anybody know of a viable, cheap solution to the problem?
0
Hi Experts,

I faced a database query issue ... I tried to solve it myself (I'm using VB.Net  VS 2015)but my solution i think that is not perform the mission well ...OK

I have a product put in more than one partition "say 2 partitions" (it must be more than 2 partitions)these partitions filled with the products as follow ...

product1  ==>>(partition1 =  5 pieces) and (partition2 =  1 pieces)   and total pieces of  product1 = 6 pieces...... and
product2  ==>>(partition10 =  1 pieces) and (partition15 =  0 pieces) and (partition3 =  0 pieces) and total pieces of  product2 = 1 piece
product3  ==>>(partition10 =  0 pieces) and (partition2 =  1 pieces) and (partition1 =  0 pieces) and total pieces of  product3 = 1 piece
product4  ==>>(partition2 =  2 pieces) and (partition3 =  1 pieces)   and total pieces of  product4 = 3 pieces
 The question is how to query in my database to find if the (total pieces of  specific product) became =1 piece
in order to make a report of the products that decreased and need to purchase  



I think this photo is enough to indicate my needs ... The blue line indicates that ProductCode '6' has 3 partitions and total pieces = 4
The Red line indicates that ProductCode '8' has 2 partitions and total pieces = 1               ........................
when i made a query on 'StockNo' in the database  i get all 'StockNo' that have 1 piece not the total pieces of   'StockNo'  for specific product code.

Any Suggestions…
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
Hi Experts,

I have an Access application linked to MS SQL 2008.

Trying to run an append query as follows

INSERT INTO Skilled_Nursing_Visit_Note ( SNV_ID, Client_Last_Name, Visit_Date, Date_Of_Birth, Medical_Record_Number, Shift_From_Hour, Shift_To_Hour, Purpose_Of_Visit_Goal, Temp, Pulse, RR, BP, O2_Saturation, O2_In_Use, Respiratory_Lung_Sounds, Respiratory_Breathing_Pattern, Bipap, Cpap, Vent, Vent_BIPAP_Settings, Cardiovascular_Skin_Color, Skin_Temp, Cardiovascular_Edema, Neuro_Alert, Neuro_Oriented, Neuro_Self_Directing, Neuro_Dev_Delay, Neuro_Deficits, Neuro_Notes, GI_Oral_Tube_Feeding, Current_Feeds, Gastric_Residuals_Present, Residual_Volume, MD_Called, Abdominal_Assessment, Feeding_Tolerance, GU_Incontinent, Number_Of_Diaper_Changes, Urine_Stool_Volume_Description, Bedbound, WheelChair, Ambulatory, Mobility_Aids, Contractures, Musculoskeletal_Notes, Pain_Score, Pain_Notes, Medication_Changes, Medication_Changes_2, Integument_Skin_Assessment, Skin_Care_Measures_Taken, Home_Environment_Safety, Treatments_Administered, Patient_Response, Instructions, Understanding, Plan_Goal, Nurse_Signature_Last_Name, Signature, Date_Signed, Nurse_Name_Stamp_SNV, Nurse_User_ID_num_SNV, Medication_Changes_Yes, Acknowledge_Accuracy, Trach, CCM_Informed, Tube_Type, Reviewed_Status, Reviewed_By, Reviewed_stamp, Client_First_Name, Nurse_Signature_First_Name, Shift_From_Minute, Shift_To_Minute, Patient_Unable_To_Sign, Reason, Status )
SELECT V_Visit_Note_Export.SNV_ID, 

Open in new window

0
Hi Experts,

I cannot export any query results in Office365, getting error attached, regardless for which name/location I choose.
Untitled.png
0
I have 5 combo boxes on Form 2 and a list box on Form. The row sources are queries. The 5 combo boxes all have the options to from text1,
 text2, text3, text4, and text5. I want to be able to select 1 of the 5 or select a combination. To display information containing text1, text2, and text3. How would you write this in vba coding?
0
I have list from all customers the daily open balance by date (I'm getting it from the Running Sum total).
I need to get only the largest Open Balance for any date for each customer.

I'm using created the following query.
It only works with little records.
But with a lot records I get an error "At most one record can be returned by this subquery"
Thanks in advance

MaxAmt: (SELECT top 1 [RunningSum ] FROM [qCustBal AddRunAmt] WHERE CustomerID=[Customer].[CustomerID] ORDER BY RunningSum Desc)
0
hi,
I'm prototyping a system in MS Access, it will act as a server and process requests in a queue.
The queue will be a series of files on the pc.
Requests will be submitted by a webform.

What reasonably quick and easy ways can I get the web requests sent to the queue?

I'd like to get a response time of say 1 second.

The request queue would be written to the web server, what would be a quick way to sync this? I could get MS access db to check every second but maybe there's a better way.

I'm new to web development so please forgive any obvious ignorance here.

Thanks
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
My MS Access VBA is stuck in an infinite loop. My keyboard does not have a Break key. How can I interrrupt the execution and enter Debug mode?
0
Hello All,
is there a way to add a sequence number into a query?
thanks a lot
0
Hello All,

using MS Access  2010
how can i make a query with a running Total not using the Dsum?
Also can i make it from a query or it has to be from a table.

thank you in advance
0
I have this VBA
            strTempFile = "C:\OrderAck\OrderAck" & strCurrentOrder & Format(Date, "DD-MM-YYYY") & ".pdf"
            DoCmd.OutputTo acOutputReport, "rpt_Automail", "*.pdf", strTempFile, False

Was working fine, now just ends on the Docmd without any error (I do have an err trap)

Anybody got any Ideas?
0
Access 2016 connected to SQL Server 2016 via ODBC DSN.
I have a macro that runs a series of Access and pass-through queries, where one of the pass-through queries appends a set of records from one SQL table to another SQL table.
One of the fields in the destination table is deliberately set with an index that does not allow duplicates.  The source table correctly has duplicates, which is fine, as I have set the index in SQL to ignore duplicates so that the pass-through query works and is not terminated.  The end result is that I get one instance of each record in the destination table, which is what I want for some interim processing.  The problem is:

Even though I have set warnings in the macro to No as the first macro action, which deals with all the action messages from all the queries, this does not stop an information message coming from SQL about these duplicates, even though it has successfully run the query and ignored them, which effectively stops my macro.  How can I ignore this message?  If I run it as a stored procedure in SQL it simply says completed, duplicates ignored.  I am not normally aloud into the SQL side so needs to be solved in Access.
0

Microsoft Access

220K

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.