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

learn more… | top users | synonyms

0
votes
0answers
39 views

Composite Primary/Clustering Key vs GUID for Merge Replication scenario

I need to decide on a primary key for my tables in a merge replication scenario (currently one laptop taken into the field during the day while another user works on a desktop; both users need read-...
0
votes
0answers
96 views

How can I layout a schema that will track monthly installments?

I'm pretty new to database design and have a beginner's understanding of SQL, mostly simple SELECT statements. I find myself in a situation where I have to create a system that will track monthly ...
1
vote
0answers
51 views

Invoice Number Generation

I started a small business and have created an accounting DB with advice from my accountant. In Part of my business I generate invoices for the same customers every month. I use a unique invoice ...
0
votes
1answer
48 views

Referring to a network location without volume label in VBA for Access 2013

I run a database at work in Access 2013. The VBA corrupted after a day from the amount of people using it, so I attempted to split the database to solve the problem. The split worked after I ...
0
votes
2answers
42 views

What would cause an UPDATE statment to hang when executing from an .mdb file to SQL Server 2008 R2 over ODBC?

Is there anything that would cause an UPDATE statement INNER JOINed between a separate MS-Access linked table and an SQL Server 2008 R2 table to hang, when the values are being set on the SQL Server ...
0
votes
0answers
67 views

MS-Access linked tables Azure intermittent connection failure

I have an MS-Access front-end with linked tables to Azure SQL db. It has been running for about 6 months without any major problems. In the last week I have had increasing intermittent connection ...
1
vote
1answer
184 views

ADO connection suddenly starts failing Intermittently

I have an Access 2010 front end connecting to a SQL Server 2014 back end. I've got SSMS 2014 installed on my machine, and everything was working fine. (Running Win7 64-bit with 64-bit Office.) Long ...
0
votes
0answers
42 views

Convert Access query to SQL Query

I am converting Access query to SQL server. I want to convert below lines to SQL 1. Format (210.6, "Standard") 2. Format (210.6, "#,##0.00") How do i convert it to SQL query. I have tried with ...
-1
votes
1answer
82 views

How do you translate this into SQL Server 2008 statement?

I'm not sure how to translate this from Access to SQL. We are currently using SQL Server 2008. I am trying to migrate Access reports into SQL views. SELECT First(IIf(IsNull([dbo_CUST_ADDRESS]![...
1
vote
1answer
52 views

Getting intervals between time from all succeeding rows? (Time is in text data type)

NAME DATE TIME INTERVAL a 10/19/2015 5:00 PM a 10/20/2015 5:00 PM 86400 a 10/20/2015 5:30 PM 1800 This is easy to do in Excel. But considering we ...
0
votes
1answer
40 views

Select Unique Fields with Max Values in Each?

I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is. Addy (text), NW (number), UDate (...
1
vote
1answer
97 views

leading 0 missing in column when exporting to csv

I have a query I export which is then used to import products into our website. One of the ways we help people find products more easily is by using a long and short item code. It's basically the ...
1
vote
0answers
6 views

select Concatenated field in Access Form [closed]

I have Created a Report whith a Listbox which contains concatenated fields: Cname: SELECT tbl_Employee.GID, tbl_Employee.Sname, tbl_Employee.Gname FROM tbl_Employee ORDER BY tbl_Employee.Sname, ...
0
votes
0answers
36 views

Shared access database file connected with MySQL server

Currently I have an access database in a dropbox folder, used by a lot of people knowing nothing of IT and DBMSes (imagine them being grannies that just learned to use access after a course of five ...
4
votes
2answers
328 views

Loop through tables or copy paste

I have to do a migration where I should retrieve data from 40 Access tables, which should contain several of the same columns (but not all, the schemas are different). To make it simpler, I used SSMA ...
1
vote
1answer
182 views

Linked servers on SQL Server 2008 R2 through linked tables in MS-Access?

We have a really old data source (file-based) in Visual FoxPro, that we currently use via linked table through an ODBC data source. Recently, we started having an issue because the latest update to ...
17
votes
3answers
404 views

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at ...
0
votes
1answer
38 views

How do I corrupt an mdb intentionally for test purposes

I'm creating a process to check for corrupt tables in an MDB on server. There's no code, just tables. I would like to corrupt an mdb intentionally for testing. Has anyone ever done this?
1
vote
1answer
32 views

MS Access 2010 adds unwanted wildcard at end of select clause

MS Access 2010 has a vey weird behaviour for me. Whenever I create a new Query, it silently adds a wildcard (i.e. ', *') to the end of the SELECT clause. The wildcard is visible only in the SQL and ...
0
votes
0answers
194 views

Converting SQL Server query to MS Access

I have the following query in SQL Server: SELECT p.Company Advertiser, COUNT(DISTINCT t.ID) [Sales Call Count], COUNT(DISTINCT j.ID) [Job Count], 100.0 * COUNT(DISTINCT j.ID) / COUNT(DISTINCT ...
0
votes
0answers
57 views

Merge two data tables with different time preferably in access

I appreciate if I could help on this issue. I am relatively beginner to database designs. I have two tables (see link below) which I would like to combine: Tbl_1: Date, Product, Sold Tbl_2: Date, ...
1
vote
0answers
789 views

SQL Server 2014 Import Wizard missing data source option

I have already run the upsizing wizard from my production Access 2010 .accdb database to create all the table structures and move the data on to our new SQL Server 2014. This included adding some PKs ...
0
votes
1answer
32 views

Access 2013 update rows from multiple rows

I need help to construct an update query to populate MynewField with contents from multiple fields and rows where the MapID is the same. RowID---MapID-----Extension----- Name------ MynewField 7 ...
3
votes
1answer
918 views

Designing a simple library database

I am currently developing a simple book library system in ms access 2007 and need some help with the database design. I want to register books, borrowers and loans, so I assume that my database could ...
1
vote
1answer
44 views

Query - Match Name to Multiple Unique Types

I'm trying to create an Access 2013 database query to display all insurance policy holders that have more than one type of insurance. I am not interested in policy holders that have only one type of ...
1
vote
1answer
170 views

How to password protect a field in access?

I'm looking to password protect a field in my database containing confidential customer information. Is there a reasonable way of doing this in a timely manner? Thanks
0
votes
2answers
51 views

Useful formats for the results of a database “audit”? [duplicate]

My company has tons of disparate databases - Oracle, SQL Server, and (shudder) MS Access - lying around, and the CIO wants to bring in a third party to track them all down and document them. But, ...
1
vote
0answers
124 views

MS Access 2010 report - SQL pivot query WHERE criteria not working

I have created a report that has a list of actions which are populated from an "Actions" table. (See Below) However, each action will have multiple updates populated from an "Updates" tables and I ...
4
votes
2answers
123 views

What flavor of SQL is used inside Microsoft Access?

I don't have much Access experience, and I took on a new Access project. I can use SQL to query the Access databases. What SQL specification/flavor is used inside Access? Is it the same as SQL Server?...
0
votes
0answers
92 views

VBA open SELECT query with parameters

How can I open a SELECT query with parameters using VBA? There exists some useful information on running insert or update queries with parameters, but the same principles don't seem to work when ...
0
votes
1answer
117 views

Linking MS Access 2010 to Outlook Inbox: Including Attachments

I'm trying to set up a MS Access table linked to an Outlook folder. I've been able to use the external data wizard to set up a linked table, but the table seems to only include subject lines and ...
1
vote
0answers
19 views

MS Access : Navigation pane is suppressed

I received a MS Access form client. It is in access 2007 format. Initially Navigation Pane was not available. Only form was visible. Then I choose options->Current database->checked Display Navigation ...
2
votes
1answer
96 views

Should I maintain relationships diagrams in the back-end and the front-end?

I’m developing an Access 2010 database application. It is split into a back-end, holding all the tables, and a front-end, holding all the queries, forms, etc. I find the Relationships tool helps ...
1
vote
0answers
74 views

Speed of Split Database vs ODBC with SQL Server vs ODBC with Azure Cloud

I would like to be able to host my data either in the cloud or in SQL Server, as having all my data in one Access file just doesn't seem the most secure to me (or am I wrong?). My forms contain many ...
3
votes
1answer
70 views

Error 7399 when the Access file has a password

I'm trying to create a linked server to access an old MS Access database (.mdb file) on SQL Server 2012, but I always get the error 7399 (authentication denied). So I tested with another mdb file that ...
1
vote
0answers
115 views

MS Access: Subform only displays first entry. Continuous Form follow-up question

I am new to MS Access and Database creation in general. Currently I am working on a simple employee database that can view an employee's information and can show their current (and previous) projects ...
2
votes
1answer
1k views

How to join all combinations from two tables with a common column

I have a list of suppliers who supply a list of countries. For example: Supplier A supplies to: USA France China and another list of suppliers with their suppliers' factories For example: ...
2
votes
0answers
27 views

Deploying access database to program files without Read only warnings? [closed]

I have an access database which I've deployed to user's computers. The database is a front end with linked tables to an SQL server database. However, I'd like to deploy the database to the user's ...
2
votes
0answers
78 views

Design Question - Stock and OnSale Table

Background : I'm designing a solution for a repair toy association. Since they have office - I'll use Access. Plot : Stock will store items - I choose this primary key (Item_Id,Record_date) ...
2
votes
2answers
495 views

MS Access 2007/2010 Compact & Repair not working

I have a database that I use in VB. As records update theres a lot of useless info in there because access doesn't reuse the same spots. So that information has to be removed. Before I would use ...
0
votes
1answer
2k views

What DSN file is MS Access using for ODBC Linked Tables?

I set up a linked tables in Microsoft Access to connects to an remote database using ODBC with a File DSN. But which DSN file is Access using now? I want to examine it and compare it with the other ...
0
votes
1answer
95 views

PostgreSQL to MS Access

Is it possible to create a database in PostgreSQL move it into other computer and use this database with MS Access? How to do that?
0
votes
2answers
42 views

Converting Access SQL HAVING command to T-SQL

I have this SELECT query I need to convert to a T-SQL view: SELECT T_Compras.Guia, T_Compras.Data, T_Compras.Id_Embarcação, T_Compras.[Nº Maré], T_ComprasDet.Id_Peixe, T_ComprasDet....
1
vote
1answer
542 views

Is it worth time converting Access query to T-SQL query

Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions. I had a database built in ...
1
vote
2answers
170 views

Minimum value on most recent date per account MS ACCESS SQL

I have a MS ACCESS DB table Riskscore with the three fields: CHACCTBS, CHRQDATE, CHSCORE and want to find the lowest CHSCORE on the most recent CHRQDATE per CHACCTBS where it is possible to have ...
2
votes
0answers
85 views

Moving from MS Access to SQL Server 2012, need help with column history

I'm moving an access database from all the data being in MS Access to MS Access being just a frontend, but I'm having trouble figuring out how to do a sort of column history. I have a tasks....
1
vote
1answer
196 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is ...
1
vote
3answers
209 views

Access Query Design View: Show results in two columns from one table

Lets say I have the follow Tables: Outfits: ID | Top Color ID | Bottom Color ID 1,2,1 2,3,4 Colors: ID | Name 1, Red 2, Blue 3, Green 4, Orange I'd like to have a query in design view to have the ...
2
votes
1answer
52 views

Distinguishing Records with Related Fields

I'm a newer user (obviously) and would greatly appreciate question writing suggestions as well as answer suggestions: I have a lot of interrelated tables which largely depend on my Products table, ...
1
vote
1answer
114 views

Concatenate columns uniquely on the same table in MS ACCESS

The original table: ======================================================================================= | A | B | C | D | E | F | G | ====...