Questions tagged [ms-access]

Microsoft Access is a relational database management system that is part of the Microsoft Office suite.

Filter by
Sorted by
Tagged with
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, ...

1 2 3
4
5
13