Tag Info

New answers tagged

2

For full details read the docs: Microsoft Access SQL reference MS Access definitely is not the same as SQL Server. The overall syntax is similar as any flavor of SQL, but it doesn't have a lot of features that were added since SQL Server 2005. It doesn't even have some pretty basic features, like: DISTINCT aggregate function references. For ...


0

Indeed, mostly SQL compatible with a few known exceptions (see the features) EDIT: More to the point you can flat out upgrade Access databases into MS-SQL Server, the syntax is virtually identical, but you have to be aware of features that Access won't support.


1

No need to use SQL that's the whole point of MS Access the Graphical Interface is sufficient for this task First create a new table with all the fields you want. Lets call it tbl01MasterTable Say the fields are GivenName, FamilyName, AddressLine1,AddressLine2, AddressLine3, Town, PostCode Say you Have 2 tables to merge tbl02BritishTable Fields are ...


2

I have never used the relationships tool for anything other than understanding the relationships between base tables on the back end database. I could see however where it would be very useful on the front end if you have many users of your application who write a lot of ad-hoc queries and you provide them queries stored on the front end database which they ...


0

The problem was the file. I got a Windows XP virtual machine that has the MS Access 95. So I could open the file to delete the current password and change groups permissions. Now the linked server works. It seems neither Microsoft.ACE.OLEDB.12.0 nor Microsoft.Jet.OLEDB.4.0 can deal with MS Access 95 security policy. But I really don't know why.


1

Open that database in 2010, and do this: Click the External Data ribbon group Click the Access item Use the Browse button to locate the database from which you want to import the database Be SURE to select the first option (i.e. the "Import tables, blah blah" option) Click OK In the Import Objects dialog, select the tables you want to import. Be sure to ...


5

Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you: Sample tables and data CREATE TABLE SupplierCountry ( SupplierName varchar(50) NOT NULL, CountryName varchar(50) NOT NULL ); INSERT SupplierCountry (SupplierName, CountryName) VALUES ('Supplier A', 'USA'), ('Supplier A', ...


1

Here is what you need to do Open the table in definition view Select a cell in the column which is empty. Add an entry of that data type (I picked the datetime in an example where this happened to me) When done, you should be able to click the data button and see all the data.


1

Another forum suggested doing it whilst saved locally rather than on a network drive. Also it is a known issue and Microsoft released this hot fix: http://support.microsoft.com/kb/952699/ It's noted that whilst the write up doesn't mention the fix, applying it does actually solve the problem.


0

Okay I did some research (which I should have done before) and I found that its a bug with Microsoft Office 2003 SP3, I found a hotfix for post SP3 that should fix this. https://social.msdn.microsoft.com/Forums/en-US/a6875337-1cd0-459a-b1b0-4747e55bdb76/corrupt-msmmmobmdb-file?forum=Offtopic


0

After experimentation, I've implemented Option 2, the boolean field for trial version. I think this solution is better normalized and more clear to users when each attribute is broken down into its own field (rather than having a version field which has to describe multiple attributes like product version and whether or not it is trial). In some way ...


0

As Sunzi mentions, the original file will be replaced after the process is finished, although this might not be your case since you're using a custom Function to go through the C&R. Another way of doing this (on A2010) would be to activate automatic compact on close (File -> Options -> Access Options -> Current Database -> Application Options -> Compact ...


1

IIRC, Access uses either a machine DSN or a file DSN. I only have Access 2016 installed but I don't think this has changed much. There should be an External Data tab in the ribbon, click on ODBC Database. This shows you two tabs: File Data Source and Machine Data Source. This image shows the MS Access Database source but I generally see folks create one ...


1

In your situation, I would just spin up a PostgreSQL database somewhere and have them connect to it in Microsoft Access via ODBC. http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html And I would keep Access and its limitations and tradeoffs in mind while designing my PostgreSQL database. Advanced features will require ...


0

A view cannot specify parameters so you need to filter when selecting from the view using a WHERE clause like Rob mentioned: CREATE VIEW dbo.View_Example AS SELECT T_Compras.Guia , T_Compras.Data , T_Compras.Id_Embarcação , T_Compras.[Nº Maré] , T_ComprasDet.Id_Peixe , T_ComprasDet.Id_Tamanho , SUM([Peso Kg] * [Caixas]) ...


0

Create the view without the HAVING clause, and when you query the view, filter it using: WHERE Guia = ???? ...with your "????" being the value that you're passing in from your form.


0

To me 30 queries does not sound like to much work in order to obtain the level of performance increase you will see having SQL Server do the work instead of Access. Especially if the data is going to expand to more users and is a more mission critical type thing. Something you might consider is running your Access database through the SQL Server Migration ...


0

You are trying to execute SQL SERVER sql syntax in msaccess. Need to remove the nolock hints. The column and table alias need to b prefixed with AS. And more. Executing your query as a pass-through is probably a better option anyway.


0

You can't have 'missing fields' in a query without getting a prompt. Your June updates can not reference the phone column that does not exist and your July can not reference address. You need 2 different queries. If your business logic can't determine which query to run then you can use the Fields Collection of the TableDef object.



Top 50 recent answers are included