Questions tagged [ms-access]
Microsoft Access is a relational database management system that is part of the Microsoft Office suite.
643
questions
2
votes
2answers
713 views
Using Powershell can you insert a macro into Access?
I am using PowerShell to automate an export of an access databse (.accdb) to postgresql. As a step of that I am using a VBA script to convert all the table and column names to lowercase so they play ...
2
votes
1answer
491 views
How to trust trust a document in access via PowerShell so I can trigger my database export marcos
Long story short I'm working on using PowerShell to automate a nightly DB conversion from access to PostgreSQL.
In it's more basic form the script does the following.
Copies the .accdb file to a ...
0
votes
1answer
573 views
MS Access M:N Relationship query: exclude records with more than the requested values
I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here:
Let's ...
1
vote
0answers
1k views
How to Insert a static value into a column in MS Access during a query
I am currently switching data sources for one of my databases, now the old data used to include a column called 'Counter' every row had a 1 in it, this 1 was then used in excel formulas and summary ...
0
votes
1answer
22 views
How to track specific item as part of a kit
Here is my situation. I have over 50 boxes each with a unique ID number. That number is tracked for inventory purposes. My problem is that inside each box there is one specific part that is tracked ...
0
votes
2answers
39 views
MySQL Allows ODBC With Any Username
I have a MySQL Community database on a remote server and I am trying to get Microsoft Access to read data from it. This data is sensitive. The problem is, MySQL doesn't care about the username that I ...
0
votes
0answers
407 views
Migrating Access 97 to Sql Server - Version Error
I have Sql Server Migration Assistant for Access v2.7, running on Windows Server 2016. On the same server I have 64bit version of Access 2016 and a 32bit version (I assume) of Access 97.
I am able ...
0
votes
1answer
83 views
Need to join two similar queries that represent different years
Rather new to Access (using 2016) and very sketchy on my SQL skill set.
First query totals sales by ALL THREE (user-supplied) criteria:
Salesman
Customer
Product_Code
[Year]
Second query does same ...
1
vote
1answer
889 views
SQL MS ACCESS: Split row into multiple based on column value
I have table (SQL MS ACCESS)
COUNTRY NO_OF_PAGES
FRANCE 2
UK 4
GERMANY 1
I want output
COUNTRY NO_OF_PAGES
FRANCE 1
FRANCE 2
UK 1
UK 2
UK 3
UK 4
...
0
votes
1answer
196 views
Access Sub Query Speed
I have three tables in Access:
employees
----------------------------------
id (pk),name
times
----------------------
id (pk),employee_id,event_time
time_notes
----------------------
id (pk),...
0
votes
1answer
504 views
Access Form: New Record command not available now [closed]
I have an access database with two forms. When the form is opened I got a pop up saying acNewRec command is not available now. I can hit end or debug. If I hit end and manually re-open the form it ...
1
vote
1answer
207 views
Access: Number format for VBA in queries of variant type
Lets say I have two queries like
select iif(condition, col, null) from table
select f(col) from table
where col is a double-valued column f is a VBA-public function(value as double) as variant. ...
0
votes
1answer
155 views
Creating a report that fills in when the user makes a data selection from a combobox?
I want to make a report that acts as an empty template, but when the user makes a selection from a combo box or pop up window, the template fills with all of that data related to that primary key.
...
1
vote
1answer
116 views
MS Access: Issue with Autonumbers in the export
I have a question that might be trivial to solve, but I have spent some time on it and didn't manage to solve it.
I am working on a database in MS Access, in which I keep track of the specifications ...
3
votes
2answers
16k views
How to compact the current MS Access database from VBA function
I want to be able to run the 'compact and repair' process from within a VBA module in the database.
I have a batch process that I run occasionally, it drops a few old tables, re-imports them from ...
0
votes
1answer
68 views
Need help designing a query to pull a list of songs based on ranking and the last play date
I have a database of a list of songs used during a performance.
The main table, tblSongs, has a Field that represents a Ranking, 1 being worst, 5 being best. The table is linked to tblEvents which ...
0
votes
1answer
69 views
Running MS Access queries for WAN hosted mySQL database
I have a front end MS Access running through a backend MySQL database via ODBC, I recently moved MySQL database from a local LAN server to a hosted WAN server on Amazon RDS. Now all of my queries are ...
3
votes
1answer
750 views
Why is it specifically Immediate If (IIF)?
I am quite new to MS Access (and databases in generally) but I have noticed that IF() statements are actually written using the IIF() function.
A quick search online told me that it stands for ...
2
votes
1answer
490 views
SQL Server Linked Table in Access DB
I'm wondering if/how an SQL Server would be impacted by an Access DB that has ODBC Read-Only linked tables from that SQL Server on it.
If a form event triggers a query on one of those linked tables, ...
2
votes
1answer
30 views
Store long number in MS access
How can I store 12 character long number in MS Access 2016, if the field cannot be empty?
0
votes
2answers
114 views
Microsoft access to Sql Server Data Migration [closed]
I need to migrate some old MS Access data to a SQL Server Database. Which tool is easier for a newbie to work with: SQL Server Import/Export or Migration assistant for access(SSMA)
0
votes
1answer
127 views
How can I turn each record on my Access table into 6 records?
I've spent all day googled one issue: please help.
I have a table like the one below that I want to expand, turning each single record into 6 records.
TableName:
-------------------------------------...
1
vote
1answer
119 views
Totals on alias columns
I have a table in MS Access that has the following fields/data:
School number Traditional Forms Sparring Traditional Weapons Combat Weapons
------------- ----------------- ---------------...
4
votes
2answers
149 views
Access not properly translating TOP predicate to ODBC/Oracle SQL
I have a MS Access query that is based on a linked ODBC table (Oracle).
The DDL for the underlying Oracle table is:
create table road_insp
(
insp_id integer,
road_id integer,
insp_date ...
3
votes
1answer
1k views
Trace ODBC commands: What is the registry path for TraceSQLMode and where is SQLOUT.TXT?
In the Optimizing Microsoft Office Access documentation, it mentions that we can:
...edit a Microsoft Windows registry setting that allows you to see
the commands that the Office Access database ...
3
votes
2answers
96 views
why the last function in msaccess allow me to bypass the group by?
I have the following query on msaccess
SELECT Trim([T13_RefSupplier_France_List].[Supplier_code]) AS Supplier_code,
Trim([T13_RefSupplier_France_List].[Art]) AS Internal_reference,
Trim(...
3
votes
1answer
281 views
Strange UPDATE results in presence of merge replication
I have an MS Access front-end database that accesses an SQL Server backend. This (as far as we can tell) works flawlessly when the SQL Server database is not replicated. Once we introduce replication (...
1
vote
0answers
32 views
Save Access SQL code to text file or Python variable?
This may not have a good answer but maybe?
So I have a number of Access databases that query a SQL Server through ODBC to generate reports. The SQL Server backend is being changed out and I need to ...
1
vote
2answers
24k views
Access Query - Count distinct values [closed]
I have a dataset similar to the one below in Access:
I am wanting to create a query which generates a table summarising how many sites each company is at. I.e. it will return something like this:
...
6
votes
2answers
22k views
Import from Access .accdb file to SQL Server [duplicate]
How to import data from Access .accdb file to SQL Server? What should I choose in SQL Server Import and Export Wizard?
Suppose we choose OLE DB Provider for SQL Server Integration Services, then if ...
1
vote
1answer
384 views
MSAccess: creating a new row in a related table and linking it back to the “master” table
In a hypothetical 'cars' database, assume there's a model table and a make table:
models
modelID int
model varchar
make int
makes
makeID int
make varchar
Model's make column has a foreign ...
1
vote
0answers
62 views
MS Access: automatic creation of row in a table based on field in another table
I have a Table A with an ID field and a Question field. The question has a yes/no answer.
I want MS Access to automatically create a new observation (row) in another table, Table B, if I answer yes. ...
1
vote
1answer
115 views
Form isn't reading table records [closed]
I am really new at building databases. I created a table that reads from other tables. However, I needed to add another table and I placed the input field into the form.
My problem is that the form ...
1
vote
1answer
14 views
How do I by pass startup code in access
I am struggling to by pass my startup code in MS Access using the SHIFT + ENTER keys. How else can I accomplish this?
1
vote
1answer
44 views
MS Access 2007: Combining data from tables caused combinations of data
So my query in Microsoft Access 2007 is taking data from three different tables: a, b, c. In these tables are double values all named as "Time" that were run to record how long an event occurred.
...
1
vote
2answers
2k views
Can I pass a single record from a recordset to a module?
I am creating a module, RedemptionEstimate, in MS Access 2016 that requires 13 parameters to be passed to it and calculates how much is owed on delinquent property taxes. Some of the parameters can be ...
2
votes
1answer
639 views
Populating one foreign key based on entry of another foreign key in Access 2016
I currently have three tables as shown below:
The user enters Certificates via a form. I want CertCounty to populate based on selection of TownshipID. I've attempted the following Data Macro:
But, ...
0
votes
2answers
4k views
Joining two tables with different fields through a link-table
Im a complete novice to Access (2013).
Im doing some History research and thought it would be easier to use Access. I have created two table so far.
People, Details info about them
Events (...
1
vote
1answer
27 views
MS-Access: How do I go about building a database with inconsistent data sources?
I'm working on building a database to aggregate various data sources and feed a set of Tableau dashboards.
The data is all employee data, the problem is, I only have one file of seven with a unique ...
0
votes
1answer
84 views
Need help with relationships in MS Access
I am in need of some assistance. I'm fairly new at access & dbs, and have managed to get a bit accomplished, but have run into a wall. I'm not sure it is my inexperience or if access cannot ...
-1
votes
1answer
497 views
How to Input a time series set of data into MS Access 2010
I am trying to create a database with time series data in MS Access 2010. More specifically, I have order history over 30 months.
What is the most efficient way to input this into MS Access? ...
1
vote
1answer
113 views
How to add data to sql server using MS access front end, when user will not always have sql connection
I have a SQL Server backend and an Access Front end database which is working fine.
However, I now have a user that needs to use this database (on a tablet) for some equipment checks out in the ...
0
votes
1answer
1k views
OleDBConnection.Close() very slow on server
I am working on a few applications which connect to an MS Access database backend (.mdb) to read/insert/update records.
Everything is working fine, but I noticed that my db operations were quite ...
1
vote
1answer
66 views
Access Db `Long Text` Unicode Byte Corruption
I have a column that is Long Text when I attempt to edit a cell it randomly inserts characters and does other weird things. The column has French HTML text in it. What could be causing this and what ...
0
votes
1answer
987 views
MS Access- Attachments to SQL Server -Images or Varchar(max)
I am trying to move a database from MS Access to SQL Server 2014 and I am using SSMA for Access. A few of the tables have attachments (pdfs, jpgs, emails, etc.).
When I move it to SQL Server, the ...
0
votes
1answer
13k views
MS-Access RANK() function
I have a table in Access like this:
ID | Op_ID
1 | 1
1 | 2
2 | 3
3 | 4
3 | 5
3 | 6
Is there a SELECT query that I can run in Access that would give me something like ...
1
vote
2answers
376 views
Emulate row count on 2 columns in Access
I have the following table in Access
I have a number of lines where there are duplicates when you filter on the first and second column. I need to remove these duplicates. The best way I found is to ...
0
votes
1answer
96 views
JOIN type for MS-Access report query
I have 7 tables, 6 of which are in a one-to-one relationship with a table named SLD joined with SLD_ID. SLD has 17 records and I need them all displayed regardless of matches in other tables.
Which ...
1
vote
0answers
477 views
Using VBA to open Access in full screen and in foreground
I have 2 issues.
I am using VBA code to create a macro in PPT that opens an Access report. The macro works but does not open it in full screen. I have tried to add the DoCmd.maximize but not sure ...
1
vote
1answer
672 views
Access Macro: ApplyFilter w/values from 2 combo boxes
I'm getting started with databases in Access for a work project (technically a work-learn for my non-CompSci degree). I have experience using multiple filters on multiple columns through Access/Excel, ...
