ms-access

Read Access File into a DataSet

Is there an easy way to read an entire Access file (.mdb) into a DataSet in .NET (specifically C# or VB)? Or at least to get a list of tables from an access file so that I can loop through it and add them one at a time into a DataSet? ...

Is there anything wrong with this query?

INSERT INTO tblExcel (ename, position, phone, email) VALUES ('Burton, Andrew', 'Web Developer / Network Assistant', '876-9259', '[email protected]') I've got an Access table that has five fields: id, ename, position, phone, and email...each one is plain text field with 50 characters, save for position which is 255 and id which is an aut...

How would you migrate hundreds of MS Access databases to a central service?

We have literally 100's of Access databases floating around the network. Some with light usage and some with quite heavy usage, and some no usage whatsoever. What we would like to do is centralise these databases onto a managed database and retain as much as possible of the reports and forms within them. The benefits of doing this would...

Best way to test a MS Access application?

With the code, forms and data inside the same database I am wondering what are the best practices to design a suite of tests for a Microsoft Access application (say for Access 2007). One of the main issues with testing forms is that only a few controls have a hwnd handle and other controls only get one they have focus, which makes autom...

MS-Access design pattern for last value for a grouping

It's common to have a table where for example the the fields are account, value, and time. What's the best design pattern for retrieving the last value for each account? Unfortunately the last keyword in a grouping gives you the last physical record in the database, not the last record by any sorting. Which means IMHO it should never ...

SQL to add column with default value - Access 2003

Updating an old ASP/Access site for a client (and hating it) - I need SQL to add a column to an existing table and set a default value. Doesn't work - any ideas? This works fine ALTER TABLE documents ADD COLUMN membersOnly NUMBER I want this to work: ALTER TABLE documents ADD COLUMN membersOnly NUMBER DEFAULT 0 Have googled and se...

Is there a .NET Control Similar to the Access 2007 Split Form?

Is there a .NET Control Similar to the Access 2007 Split Form? Or has anyone built such a control? I upgraded a small personal Name and Address DB to Access 2007 and noticed the Form had a property called “Default View” which can be set to “Split Form”. “Split Form” mode has a GridView and a Form together in one control. When a record ...

How can I lock down my MS-SQL DB from my users and yet still access it through ODBC?

I've got an ms-access application that's accessing and ms-sql db through an ODBC connection. I'm trying to force my users to update the data only through the application portion, but I don't care if they read the data directly or through their own custom ms-access db (they use it for creating ad hoc reports). What I'm looking for is a w...

How do I get ms-access to connect to ms-sql as a different user?

How do I get ms-access to connect (through ODBC) to an ms-sql database as a different user than their Active Directory ID? I don't want to specify an account in the ODBC connection, I want to do it on the ms-access side to hide it from my users. Doing it in the ODBC connection would put me right back in to the original situation I'm tr...

How to Prevent the "Please tell Microsoft about this problem" Dialog Boxes

We have an error that we can't seem to find and don't have the need/resources to try and track it down. What we do need to do is just keep the freaking "Please tell Microsoft about this problem" dialog boxes from cluttering up the server. It is from an MS-Access error (we think) but I can't find where Access is installed (I don't think...

Keyboard Shortcut in Access 2003

Is there a keyboard shortcut in Access 2003 that will run a query while in design or sql mode? ...

MS Access ADP Autonumber

I am getting the following error in an MS Access ADP when trying to add a record on a form linked to a MS SQL Server 2000 table: Run-time error '31004': The value of an (AutoNumber) field cannot be retrived prior to being saved. Please save the record that contains the (AutoNumber) field prior to performing this act...

Is there any way to create multiple insert statements in a ms-access query?

I am using MS Access 2003. I want to run a lot of insert SQL statements in what is called 'Query' in MS Access. Is there any easy(or indeed any way) to do it? ...

Importing Access data into SQL Server using ColdFusion

This should be simple. I'm trying to import data from Access into SQL Server. I don't have direct access to the SQL Server database - it's on GoDaddy and they only allow web access. So I can't use the Management Studio tools, or other third-party Access upsizing programs that require remote access to the database. I wrote a query on the...

Binding a form combo box in Access 2007

I've created an Access 2007 form that displays, for example, Products from a Product table. One of the fields in the Product table is a CategoryID that corresponds to this product's parent category. In the form, the CategoryID needs to be represented as a combo box that is bound to the Category table. The idea here is pretty straightf...

MS Access - what are the lowest required permissions for the backend file and for the folder containing it

I maintain an ms-access application splitted to frontend and backend files. The frontend file is in the users conputers. The backend file is in a shared folder in the server. What is the lowest permissions required? can I give some of the users only read-only permissions in that folder? (or hide it from them in some other way) but sti...

MS Access - Controlling another database in VBA

I have the following code: Dim obj As New Access.Application obj.OpenCurrentDatabase (CurrentProject.Path & "\Working.mdb") obj.Run "Routine" obj.CloseCurrentDatabase Set obj = Nothing The problem I'm experimenting is a pop-up that tells me Access can't set the focus on the other database. As you can see from the code, I want to run a...

After connection ona access database encode data from iso-8859-2 to utf-8

I have database in iso-8859-2 format. But I need to create XML in utf-8. I almost don't know anything about asp.net so I need HELP! I have database in iso-8859-2, but I need to create xml file in utf-8. That means tha I must encode database before prinitng in UTF-8! Thanks In PHP goes like this: db_connect(); mysql_query("SET NAMES 'UTF...

How would you create a database in microsoft access that is searchable only by certain fields?

How would you create a database in Microsoft Access that is searchable only by certain fields and controlled by only a few (necessary) text boxes and check boxes on a form so it is easy to use - no difficult queries? Example: You have several text boxes and several corresponding check boxes on a form, and when the check box next to the...

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this: INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES (SELECT LongIntColumn1, Avg(CurrencyColumn) ...