Can you create a query in MS Access using a SQL Statement?

Microsoft Access is designed such that even if you do not know SQL or SQL syntax you could still use the application. As a consequence, there is no obvious starting point to write a SQL Statement and execute it. However, it does allow you to write a SQL Statement, save the query and execute it.

Open access application. I have just one simple table 'BOOK1'.

In the 'Queries' tab of Create click on Query Design to open a tabbed page 'Query 1" as shown.

Query 1 has two panes. The top one is empty and it is here you can write a SQL Statement and the bottom pane is where you can compose a query using visual elements.

Right-click in the empty top pane to show this drop-down.

Click on 'SQL View'. This view is not the same as 'View' in SQL Server. This opens a text editor where you can write a query. Note that it has just the 'SELECT'. It expects you to write a "SELECT" statement.

Note that there is a Table 1 called 'Book1' shown here.

Now I write a SQL query against this table.

SELECT ID, Field2 

From Book1

Here is the statement in the SQL View's editor pane.

Now save the query, Query1. You need to click on the tab to save as shown.

You can save it with a different name which is usually recommended. I saved it as BookQry as shown.

Now double click on BookQry on the left to open the query to show the result.

How can we convert the Excel data table to Access?

 Excel Table is Excel data converted to a table.

Excel application allows you to export excel data in an Excel sheet in two ways.

1. Create PDF/XPS Document

2. Allows you to change the File Type

The following filetypes are possible:

There is no direct way you can go from Excel to Access but there are a number of ways you can do.

Here is a simple procedure of taking Excel data to Access.

We start with a simplest data in a sheeet. Use File|SaveAs and save it as a CSV file shown here.

Create a new database from Access

Now all you need to do is to import into an Access application.

Open the Access database and click on External Data. Now click New Data Source as shown and from going to submneu you can get data from a file saved in several formats incouding CSV. Click on File|Excel.

This opens up 'Get External Data-Excel Spreadsheet'. Browse and select the CSV file you created and accept the default for import, Import the source data into a new table in the current database.

Huh! Editor sucks!!

Click OK. The Import Spreadsheet Wizard comes up showing your data in the Excel sheet.

Click Finish. Your data is in an Access table as shown.

If you need to bring in your data, follow the steps. For problems contact me.

Can you access data on a web page using Microsoft PowerBI?

You can not only access data on a web page but also from very many sources shown here:

Briefly from the menu item Get Data after you launch the Power BI Desktop, you can access Web in the Get Data window. When you insert the URL of the page from which you want to extract data, the Power BI program will display all the available data in the tables in the web page in its Navigator menu.

Once you have the tables listed in the Navigator menu, just choose the table or tables and load them to the Power BI for processing. You can then query the data that you just brought in and then create the pages of reports you want to create. It is that easy.

Here is an example from a web page (Wikipedia).


Here is an example fro SAP anywhere Server:


Here is an example from a text file using Power BI


You can find many more examples from my blog here:
https://hodentekmsss.blogspot.com

How can I connect to an Microsoft EXCEL file on my Windows 8.1 laptop?

If you have Office running on your laptop it should be immediately possible and you would not be asking this question. Although you do not have Office sometimes you may want to connect to it.
For example you may use a Export/Import wizard to create a Excel file from the data on your SQL server. In such cases you come up with the following error:

Excel11

You will find that there are no ODBC (x64) drivers on your machine which will enable to correct this error and you need to download and register them on your computer. Open ODBC Manager on your computer and verify.

 

Internet search reveals that you can download the Access driver (Microsoft Access Database Engine 2010 Redistributable) from here:
http://www.microsoft.com/en-us/download/details.aspx?id=13255


AccessDriver

The details of this driver are shown here from the Microsoft site. This installation will provide drivers for office applicaitions including MS ACCESS and MS EXCEL.

Download and install the AccessDatabaseEngine_x64.exe file and double click the file to run the program.

Excel 22

This will install the Microsoft EXCEL and Microsoft ACCESS drivers needed for connectivity.



 

How does an Access table copy over to MS SQL Server 2008?

Here is an example of how Categories table in Northwind database would copy over to the SQL Server 2008 Database Nwind2008.

MS Access:

Category ID  Primary Key Data Type: AutoNumber Long Integer Indexed:Yes(No dups)
CategoryName: Data Type: Text FieldSize:15 AllowZeroLength:No Indexed:Yes(No dups)
Required:Yes
Descripton: Data Type:Memo AllowZeroLength+NO, Indexed:No, Required:no
Picture: OLE Object Required:NO

SQL Server 2008:
CategoryID: Int AllowNulls:NO
CategoryName: nvarchar(15) AllowNulls:No
Description: nvarchar(MAX) allowNulls:Yes
Picture: Data Type: image Allow Nulls:Yes