Click here to Skip to main content
15,867,308 members
Everything / Productivity Apps and Services / Microsoft Office / Microsoft Access

Microsoft Access

MS-Access

Great Reads

by William Hey
Classical number theories
by Rob Culhane
How to dynamically add access database columns at runtime using VB.NET
by William Hey
Semi-Prime Ordered Sequences (Part 2) is the follow-on to “Exploring Computational Number Theory (Part 1)” and describes a process for ordering the semi-prime base sequences.
by Clifford Nelson
This is a tip that has the code for selecting an Excel file and sheet for import into Microsoft Access. This includes the code required to browse for a file, and populate a ListBox with the sheets of the Excel Workbook, and the code to import an Excel spreadsheet.

Latest Articles

by DotNetLead.com
Azure AD for authentication and authorization of users for your website
by Gustav Brock
Format amounts in VBA using the Indian number format for currency and amounts
by Clifford Nelson
Method to change source of a Form’s Subform source to a Query or Table in Microsoft Access

All Articles

Sort by Score

Microsoft Access 

21 Apr 2013 by Rob Culhane
How to dynamically add access database columns at runtime using VB.NET
31 May 2016 by William Hey
Semi-Prime Ordered Sequences (Part 2) is the follow-on to “Exploring Computational Number Theory (Part 1)” and describes a process for ordering the semi-prime base sequences.
7 Sep 2018 by Clifford Nelson
This is a tip that has the code for selecting an Excel file and sheet for import into Microsoft Access. This includes the code required to browse for a file, and populate a ListBox with the sheets of the Excel Workbook, and the code to import an Excel spreadsheet.
17 Jul 2011 by OriginalGriff
Spaces, first and foremost.Put '[' and ']' around your field names:string updateQuery = "Update Register Set [First Name] = '" + firstNameTextBox.Text + "',[Sure Name] = '..."Then read up on SQL Injection Attacks and change it to Parametrized queries before someone accidentally or...
20 Jun 2012 by Maciej Los
This should works:SELECT CardNo, VehicleNO, MAX(ExpiryDate) AS ExpiryDateFROM YourTableGROUP BY CardNo, VehicleNOORDER BY CardNo, VehicleNO
26 Jan 2010 by #realJSOP
A lot of your classmates have already asked this question. Search the C# forum for "matlab".
25 Mar 2011 by Dylan Morley
A few issues here. 1) Your balance is wrong. Within the report details, you're just subtracting [debit]-[credit]. You need to create a running balance. calculate-a-running-sum-cumulative-total[^]That's easy in Access 2007, just put a text box on the report and set it's 'RunningSum'...
17 Jul 2011 by Shameel
There are two problems here. The first one is column names with a space in them, for example, 'First Name' column. You probably have to enclose such names in a square brackets. The second problem is, if any of the numberic text box is empty, you can get this error. For example, if...
8 Mar 2012 by Torsten Tiedt
Avoid event spaghetti code when business logic becomes complex.
20 Jun 2012 by Prasad_Kulkarni
Try this:SELECT CardNo, VehicleNo, expiryDate FROM YourTableName t1 WHERE expiryDate = (SELECT MAX(expiryDate) FROM YourTableName t2 WHERE t1.CardNo = t2.CardNo)
5 Sep 2014 by CB Sharma
Use below querystring query = "update DefaultProfile set [Password]='abc123' where RegNo='admin'";
5 Sep 2014 by OriginalGriff
"Password" is a reserved word: you need to escape it:string query = "update DefaultProfile set [Password]='abc123' where RegNo='admin'";But... don't do it like that!1) Don't pass values to and database as strings, particularly if they may (as these will) originate with the user. Use...
23 Apr 2020 by Maciej Los
string query = "SELECT * FROM [TRANSACTION] WHERE ([DT1] BETWEEN #" + fromdate + "# AND #" + todate + "#) OR ([DT2] BETWEEN #" + fromdate + "# AND #" + todate + "#)"; Such of query is SqlInjection[^] vulnerable! Rather than it, use...
26 Jan 2010 by Smithers-Jones
Maybe you should learn the meaning of "Question" first, before doing anything else. It's not the same as "Enter your name here".
20 Apr 2010 by DaveyM69
Along the lines of Michel's suggestion, you should be using the ApplicationData or CommonApplicationData folders.If you decide to go this route, this tip[^] may help.
24 Feb 2011 by khurem
Hello All,We are facing an issue with MS access ADP project (SQL SERVER 2000 on backend) in production environment. Currently 20 users are using the application and they have their own copies of access application, when any user opens the application it creates three new connections with the...
23 Mar 2011 by OriginalGriff
Access provides autonumber: it is called an "autoNumber" field, surprise, surprise. Also unsurprisingly, it is numeric only...I would keep the Voucher code separate: The Voucher number is the Autonumber field, the Date field is the TodayDate, and build the voucher code from that using...
4 May 2011 by RaviRanjanKr
Try given linksLinqConnect Tutorial for SQLLite[^]Linq to MS Access[^]LinqToSQL: Comprehensive Support for SQLite, Microsoft Access, SQServer2000/2005[^]
29 May 2011 by Abhinav S
Some links -Storing and Retrieving Images from SQL Server using Microsoft .NET[^]http://www.shabdar.org/sql-server/105-store-save-images-in-sql-server.html[^]Save An Image Into SQL Server 2000...
2 Sep 2011 by Abhinav S
StepsStep 1) You need to host the database in central server / location and allow client programs to access it.Step 2) You will need to make sure all clients have network access to this location.Step 3) To do this, you will need to setup a database connection, say an ODBC setup, on every...
19 Dec 2011 by Wendelius
Is the error "Path not found" referring to your access database location. If that's the case, check that your program is referring to correct location and preferably define the location in a configuration file.Side-note: If this is a new program consider using VB.NET instead of VB6 which...
15 Jan 2012 by Prasad_Kulkarni
Hi,Try this;How to add client side calculation in GridView[^]calculating-gridview-total-using-javascript-jquery[^]
29 May 2012 by Anoop Ananthan
Yes, the queries need to seperated by semi-colon ";"eg:string query = "insert into table1(x,y,z); update table2 set a = 23;"OledbCommand cmd = new OledbCommand(query, connection);...cmd.ExecuteNonQuery();
29 May 2012 by Prasad_Kulkarni
Yes, it can be; Microsoft Access supports Transactions [^]and they work quite well.Even so, if possible, use SQL Server Express. It's much more powerful than Access.
13 Sep 2012 by Sandeep Mewara
Here, refer this: MS Support: How to convert an Access database to SQL Server[^]In case you need any help, you can refer: Free Microsoft SQL Server Database Migration Assistant[^]
13 Oct 2012 by OriginalGriff
Try:SELECT LastName + ', ' + FirstName + ' ' + ISNULL(MiddleName, '') AS NumeComplet, ClientId FROM Clients ORDER BY LastName
4 Aug 2013 by Mehdi Gholam
You need a (') after your values:string str = "insert into contact(file_no, full_name, designation, department, company_name, contact_type, categories, street, city, pincode, assistant_name, ph_home, ph_mobile, ph_bus_office, ph_bus_res, fax, email_id, website) values('" + ...
9 Apr 2014 by Sergey Alexandrovich Kryukov
This way of writing queries is wrong from the very beginning. You are composing a query concatenating strings taken from UI. Among other problems, this way, you invite a well-known exploit called SQL injection. This is how:http://xkcd.com/327[^].For further detail, please see my past...
24 Jun 2014 by Bhis
Back in 2008, I was doing some freelance work and had a chance to work on this MS Access Report project. The client dealt with employee benefits, claims, and payment reimbursements.
11 Oct 2014 by Dave Kreskowiak
You can TRY to use Access as a multiuser database but it's not stable.Do yourself a HUGE favor as scrap Access and go with SQL Server instead.
26 Oct 2014 by Maciej Los
MS SQL:Download MS SQL Server Migration Assistant for Access[^], install it and use it ;)Oracle:This tutorial: Migrating a Microsoft Access Database to Oracle Database 11g[^] explains how to do it in a simple way.
26 Feb 2015 by Maciej Los
Client computers does not need Access to be installed. .Net libraries support many database providers. See: .NET Framework Data Providers[^]To be able to use MS Access as a database, you need to use OLEDB drivers. That's all. Note: please, carefully read Sergey's answer. There is a lot of...
10 Apr 2015 by Sergey Alexandrovich Kryukov
It looks like everything is correct. All you might have missing is correct naming of the constant. (Again, please review my previous answer to the question you referenced, in part of namespaces.)This is how you can use this constant in the most general case, independent from content. It...
15 May 2015 by OriginalGriff
You can't. A short text field only holds up to 255 characters - so if you need more, you need to use a different column type.
8 Sep 2015 by OriginalGriff
Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]There is even a warning how stringly people feel about this: Code based passwords[^]Fixing that fixes your case sensitivity: the hash value...
15 Jan 2016 by Wendelius
Since you're joining the two tables you will get as many rows in the result set how many rows satisfy the join condition. In other words, if one record has two attachments, two rows are returned.-The rows aren't duplicates. If you have a look at the columns coming from attachment table you...
16 Feb 2016 by Dave Kreskowiak
For the umpteen billionth time this question has been asked, Google for "C# parameterized queries" to learn how to fix this little problem.Also, you might want to Google for "SQL Injection Attack" to find out why using string concatenation to build your SQL statements risks destroying your...
25 Feb 2016 by Dave Kreskowiak
Why are you using DAO?? It's been dead for quite a long time now.The last version of Office to expose it's functionality, oddly enough, was 2010. DAO support doesn't exist in Office 2013. That's why it fails.You should have been using OleDb and the ACE engine for an Access database.
2 Jul 2016 by Six Hat Solutions
Easily Send Emails From Your Microsoft Applications Using VBA and the SendGrid API
8 Sep 2016 by Dave Kreskowiak
Restore from backup. There is no recovery.
8 Jun 2018 by Maciej Los
As far as i know, MS Access does not support DELETE + JOIN. You have to change your sql command to below form: DELETE Table1.* FROM Table1 WHERE EXISTS( Select 1 From Table2 Where Table2.Name = Table1.Name ) But, when i did look on you query again, i think you need only: DELETE FROM student...
23 Feb 2019 by OriginalGriff
I don't care what your mentor says: that's dangerous. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead. When you concatenate strings, you...
16 Sep 2019 by Richard MacCutchan
You most likely need to upgrade to the latest Access DB engine: Download Microsoft Access Database Engine 2016 Redistributable from Official Microsoft Download Center[^].
16 Jun 2020 by OriginalGriff
Password is an SQL reserved word, and shouldn't be used as a column name. If it is, you need to escape it every time you use it: string cmdstring = "INSERT INTO userID (UserName, [Password]) Values (@User, @Password)"; But you should never...
7 Apr 2021 by CHill60
In theory it is possible - try searching using your favourite search engine with Quote: MS Access Split string into multiple records You will need to write some VBA to do this. However, that would not be a good solution. A better solution is for...
16 Feb 2010 by Bijon Kumar
http://blsys.n...
20 Apr 2010 by Michel Godfroid
You should not be doing this. I know a lot of applications did this under XP, but this kind of behaviour forces you to run your app as Administrator under Vista and Weven.If you want to share data between all users of the application, your installer should query the user for a path where that...
10 Jun 2010 by PSK_
I think this is what your are looking for.SELECT Temp.siloid, temp.tdate,MAIN.amountFROM( select siloid, MAX(tdate) AS tdate from dbo.siolstate group by siloid) TempINNER JOIN dbo.siolstate MAIN ON MAIN.siloid = Temp.siloid AND MAIN.tdate = Temp.tdate
19 Feb 2015 by DrABELL
Generate large data series by using Cartesian Product and small data table
21 Dec 2010 by OriginalGriff
I would suggest using parameterised queries - it would look a lot neater, and may get rid of the problem:string QueryDB = "INSERT INTO Details (Order, ProductCode, ProductName, Tray, Index, Depth, DeviceID, IO) VALUES (@OR, @PC, @PN, @TR, @IN, @DP, @DID, @IO)";dbCommand = new...
24 Dec 2010 by thatraja
can you make your question clearly?You Tag says "Access" also question & your code says "Sql". which database?For your issue use Transactions in Code or database.Use database transactions in VB .NET[^]SQL Server Transactions and Error Handling[^]
5 Jan 2011 by Espen Harlinn
Use parameters[^] instead of string concatenation.RegardsEspen Harlinn
5 Jan 2011 by JOAT-MON
The problem is that the BOOK_NAME has an apostrophe (single quote) in it, which is the SQL string delimiter, causing the rest of your insert to be misaligned. To escape the default behavior you need to replace the single quote ' with two single quotes '' on any strings you insert into the...
2 Feb 2011 by OriginalGriff
Handle the TextBox.TextChanged[^] event. Once you have set the textbox content as required in your Form.Load or Form constructor, link in to handle the event for all three TextBoxes: textBox1.TextChanged += new EventHandler(TextBox_TextChanged); textBox2.TextChanged += new...
9 Feb 2011 by JF2015
You need to check the registry for the following key:"HKEY_CLASSES_ROOT\Access.Application\CurVer"The key will give you the following values:Access.Application.8: means: "Access 97"Access.Application.9: means: "Access 2000"Access.Application.10 means: "Access...
10 Feb 2011 by Nish Nishant
Didn't you post a similar question recently?I don't think InitialFileName will work as expected all the time. You need to use FileDialog.SelectedItems. In your case since it will be a single filename, you just need to access the first item in that collection.
28 Feb 2011 by RaviRanjanKr
Without Seeing the Name of your Table and its Column Value its very difficult to write a Program whatever you can take a help of given command to load value in textbox after selecting data in ListviewOleDbCommand cmd = new OleDbCommand("select * from TableName where custID...
28 Feb 2011 by Dima Popov
You can expand Olivier's solution using a custom class:private void yourListView_SelectedIndexChanged(object sender, EventArgs e){ if (yourListView.SelectedIndex == -1) return; Customer customer =...
5 Mar 2011 by m@dhu
Instead of deleting from one table and Inserting into another just use an extra column in table1 which gives the status of the record. Just change the status of the record to 'deleted/disabled' if you want to delete the record.
20 Mar 2011 by Wendelius
"I need to track who is on and when they logoff, and what day it was"If you want to track logons/logoffs to the computer, why not just use Windows Security log?"I found your solution for a site, is it possible to use this code for a computer, and if so, how would I do it, My knowledge of...
4 Apr 2011 by Wendelius
First, if you have a choice, don't use VB6 which is a legacy environment. So if possible shift to VB.NET.Secondly, Access is basically just a flat file so what you need is access to the file itself. You can map the share using both IP or UNC or use the file without mapping. Thirdly, you...
18 Apr 2011 by #realJSOP
We're not here to write code for you. Write the code, try to make it work. If you have a specific question about a specific part of your code that is not working, come back here and ask that question.
18 Jun 2011 by Dr.Walt Fair, PE
See this example[^] using SQL.
28 Jun 2011 by Dr.Walt Fair, PE
I'm not sure if I understand. Have you looked at using AUTOINCREMENT[^]? You can change the starting value if needed, but I'm not sure that's what you're looking for.
9 Jul 2011 by thatraja
You may use Gridview control with Detailsview or Formview. Looks like you didn't try with right keywords in search engines. I found this in CP. GridView-DetailsView (Master/Detail) Control[^]
15 Aug 2011 by Simon_Whale
I would do the following1. Get the SQL Statement that is dynamically generated and run it directly on your access database to make sure there is no errors in your sql string. i.e. INSERT INTO tbl_Mine ( MINE_NAME, Mojaveze_Bahrebardari_No, Mojaveze_Bahrebardari_Date, Geo_Location,...
20 Aug 2011 by thatraja
Post this question in Site Bugs / Suggestions[^] forum. I have a dirty suggestion, create a new account in CP. Also you can delete your old account by CP admin(Once you created your new account).
2 Sep 2011 by Wendelius
To add to Abhinav's answer, since this is an Access project, the client needs to have direct access to the database files. This means that you most likely have to share the folder where the files are located.For the future. If you plan to have more clients and you want to achieve more secure...
3 Oct 2011 by Adam Covitch
It is common to configure back-end database tables to contain a column with an auto-generated ID unique to each row. This tip describes how to sync the ID generated by the database back to the application layer.
17 Oct 2011 by Chuck O'Toole
All I see is you sticking a "string of characters that looks suspiciously like an SQL Query" into a string variable and printing that out.I see nothing that issues an SQL Query on the string itself so why would you expect a result?
18 Oct 2011 by Chuck O'Toole
Get out the big hammer and typcast it to the desired datatypeSQLCHAR* query = (SQLCHAR *)"SELECT tblIP.[IPAddress], tblIP.[IPStatus], tblIP.[IPType] FROM tblIP ORDER BY tblIP.[IPAddress] ASC;";
4 Nov 2011 by André Kraak
If you are working with Access 2010 you can use Data Macros, as described in this blog Access 2010 data macros (similar to triggers)[^].
19 Dec 2011 by Wendelius
You could for example use Timer[^] class and it's Elapsed[^] event.However, if you're loading something in your code, I think it would be better to show the progress based on what is executed in code rather than on predefined time which may vary on different systems/machines.
19 Dec 2011 by thatraja
I think you got this error Path not found (Error 76)[^]I think you have used hard-code path(Ex. F:\Database\db.mdb). If your target machine doesn't contain the F drive then it'll show the above error message.My suggestion is use App.Path[^]So your connection-string will be like as...
4 Jan 2023 by The_Life
FriendsI have created a table in ms-access table.in that i created the invoiceno field as autonumber.now i finished the project.i deleted all the testing data in the table.while im deleting the autonumber was 1027after deleting the data completely from the table.i tried to insert a...
15 Jan 2012 by Anuja Pawar Indore
See this threadhttp://stackoverflow.com/questions/2522750/calculate-total-value-in-gridvew-in-asp-net-c-sharp[^]
13 Mar 2012 by OriginalGriff
You can't, unless he tells you the encryption key and you use the same or equivalent software.That is, after all, the whole point of encryption...
28 Mar 2012 by R. Giskard Reventlov
I think you may find you need to add a couple of spaces and reformat the string:cmd.CommandText = "insert into " + inp_table + " ([date],[open],[high],[low],[close],[volume]) values(@date, @open,@high,@low,@close,@volume)";I won't mention stored procedures. :-)
28 Mar 2012 by OriginalGriff
Check the value of edited_name - it is probably blank or null, which is why it is complaining. And why the original, dangerous code deleted without replacing.
29 Mar 2012 by Sebastian T Xavier
Your code seems to be incomplete....please use between for better date comparisons...Regards
12 Apr 2012 by Wes Aday
using System.Data.OleDb; string connection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=yourpath\10_stars.accdb"; using (OleDbConnection con = new OleDbConnection(connection)) { con.Open(); OleDbCommand cmd = new...
6 Jun 2012 by Tim Corey
In SQL, if you want the count of a value when it is greater than zero, you would do it like so:SELECT SUM(CASE WHEN Amount > 0 THEN 1 ELSE 0 END) AS AmountCount, NameFROM tableNameGROUP BY NameYou would do the SUM statement for each column you wanted the count on. What it does is it...
20 Jun 2012 by Prosan
use this query it will work i have checked it on access.SELECT str(datepart("d",Start_date)) +'/'+ str(datepart("m",Start_date))FROM tblworkand you can use this query alsoSELECT mid( format(Start_date,'dd/MM/yyyy'),1,5)FROM tblworkWHERE len(Start_date)>0;
28 Jun 2012 by OriginalGriff
The max limit for an Access 2007 is 2GB. You cannot exceed that.http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx[^]You would have to look at your data, and work out some way in which it could be partitioned into multiple databases.Or, dump Access...
12 Aug 2012 by Philip Stuyck
If nothing is deleted it must be because your Id is wrong. Are you sure the parameter is filled in correctly and that this value actually exists.ExecuteQuery has a return value with the number of updates that have been done. Very usefull information during debugging!Also check your sql...
12 Aug 2012 by AshishChaudha
Please check the following line while debbuging, are you getting any value in DataGridView1.SelectedCells(0).Value.cmd.Parameters.Add(New OleDbParameter("@ID", OleDbType.Integer)).Value = DataGridView1.SelectedCells(0).ValueThanksAshish
17 Sep 2012 by OriginalGriff
Do not try to store any data in the Program location, or any folder below that! It worked in XP and below, but since Vista, you require admin privileges in order to modify anything under Program Files.There are loads of places you can store program data, which are much more sensible (if only...
17 Sep 2012 by venumailorg
In the solution explorer, go to the references folder, select all the references within it, right-click and set the property CopyToLocal as true. Then deploy and you will have all the referred dlls.Regards,Venugopal
13 Oct 2012 by Sandeep Mewara
Have a look at the following blog entry, if it helps: Microsoft Access was unable to create the .accde, .mde or .ade file [^]
14 Oct 2012 by OriginalGriff
This is the SQL version I use - the access version is the same, except you replace the SqlConnection and SqlCommand with whatever you are using. using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand com = new...
30 Oct 2012 by OriginalGriff
Re-install Crystal Reports - or more likely, install it to start with! :laugh:
17 Dec 2012 by Chiranthaka Sampath
I have created an application that connects to an MS Access Database. But I have also used an another class that holds the database connection variables. it is as the followingusing System.Text;using System.Data.OleDb;using System.Data.DataTable;namespace Lanka_Lab{ public...
1 Jan 2013 by Sheikh Muhammad Haris
Since DropBox requires Authentication, therefore it does not allows you to get data from that location.Use your System Location where your dropbox saves files Locally as you have mentioned above.
24 Mar 2013 by OriginalGriff
I would use a text based field for both the telephone number and the Zip / Postal code.Not all phone numbers are just numeric digits: you can also add '(', ')' brackets to delimit an area code, '+' for International code prefixes, and spaces for human readability. In addition, some start with...
25 Mar 2013 by Maciej Los
Please, check:1) what is returned by dialog.FileName. It should be the full filename of another database.2) does version of both databases are equal? If not, connection string might be differ.[EDIT]I have done it in this way and it works perfect:using System;using...
7 Apr 2013 by Maciej Los
Some useful information you'll find here: http://office.microsoft.com/en-us/access-help/set-a-default-value-for-a-field-or-control-mdb-HP005188852.aspx[^]You don't need to set default value for field in database table, because, you can always set "default value" in query:PARAMETERS p1 INT,...
17 Apr 2013 by Johnny J.
Adaption of pdoxtrader's codePublic Function BusIdExists(ByVal busid As String) As Boolean Dim thisSql As String = "SELECT Count(*) FROM [yourtable] WHERE busid=@busid" Dim rowCount As Integer ' Get the list of rows with that busid Using conn As New...
7 May 2013 by OriginalGriff
Personally, I wouldn't do it that way: I would probably store it as a pair of DateTime objects:Work_Date and Alarm_date - and set the Alarm date based on the selected time interval when the user inserts it. That way, the database doesn't need to know what range of intervals your application is...