ms-access

access: how to detect with VBA whether a query is opened?

is it possible to detect whether there is an open query using VBA in access-2007? i am opening a query like this: stDocName = "Meeting_Reasons_Frequency" DoCmd.OpenQuery stDocName is it possible to detect whether it is open? ...

creation of database through ODBC takes a very long time in sql server

i am exporting a datasheet from access into sql server 2008. it exports it fine, but it takes a very long amount of time for me to actually see it in SSMS. does anyone know why it takes so long? i dont know how long it takes because it's been about 10 minutes, but i do know that it was created since i was trying to export the same thing ...

Access MsysObjects values -32758, -32757 and 3

I'm quering the table MsysObjects for making a list of the objects in my database: SELECT MsysObjects.Name, MsysObjects.Type FROM MsysObjects WHERE (((Left$([Name],1))<>'~') AND ((Left$([Name],4))<>'Msys')) ORDER BY MsysObjects.Name; I know these values: -32768 = Form -32766 = Macro -32764 = Report -32761 = Module 1 = Table 5 = Query...

MS Access: .Close required after .RecordSetClone ?

In an MS-Access form, I have the following code attached to an event: Dim frm As Form, rs As Recordset Set rs = Me.RecordsetClone 'do some stuff with rs ' rs.Close Set rs = Nothing Set frm = Nothing My question is: is the rs.Close required ? Is it even good (undesired side effects ?) ...

Escape input data in SQL queries when using ODBC + Access

I've tried odbc_prepare() + odbc_execute() to update a record in an Access file but I always get an SQL state 07001 error message about incorrect column count (actually, the message is in Spanglish and doesn't make much sense): <?php $items = array(); $items[100] = 'Foo'; $items[200] = 'Bar'; $sql = 'UPDATE street SET name=? WHE...

Comparing SQL date without current month

Hi! I have a table in Access with string columns and a date column. I want to get all the rows from the table when the date is lower than 22.10.2010, except this month. So, i need the rows from 30.09.2010 to ... I tied something, but I figured out it's not right: SELECT name FROM table WHERE YEAR(date)<=2010 AND MONTH(date)<10 But ...

SQL JOIN problem in MS Access 2007

SELECT DISTINCT bw.Bor_name FROM Borrower AS bw, Loan AS l JOIN Book_Copy AS bc ON l.Bc_id = bc.Bc_id WHERE bw.Bor_id = l.Bor_id GROUP BY l.Bor_id, bc.Bt_id HAVING COUNT( bc.Bt_id ) > 1 AND COUNT( l.Bor_id ) > 1; This works perfectly in a MySQL testing environment but won't work in MS Access 2007 where I actually need it run. I have a ...

SQL Server 2008: why do I see only system tables when I login ODBC from ACCESS

I am using Access to try to get some links to tables in SQL Server 2008. I've created a user name under security in SSMS for SQL Server and I've mapped it to a user that is under the database that I need. I've also given that user a default database that I need. When I try to connect through ODBC I am not seeing the correct tables. Wha...

SQL Server 2008: ODBC connection problems

I am able to successfully connect from access 2007 into SQL Server 2008 using ODBC; however it prompts me with the password each time. When I try to open the linked table from access it says connection filed: SQLSTATE '28000' SQL SERVER ERROR 18452 [microsoft][odbc.........[][]] login failed. the loin is frmo an untrusted domain and can...

A2K Error 3011 on TransferSpreadsheet method

I'm jut trying to import a spreadsheet into a table in Access 2000. The spreadsheet is called cc-ledgcodebalances.xls with no field names. My code is DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "tmptmp", VFileName, 0 This seemed to work once but since then I now get this; The Microsoft Jet database engine could not ...

SQL Server 2008: should I be using Windows auth or SQL Server auth?

I have an MS-Access 2007 front end. I will have multiple users on it. They are all going to be on the network company domain. Should I be using Windows authentication or SQL Server authentication to be connecting to SQL Server 2008 via ODBC? ...

Emulating IBM floating point multiplication/addition in VBA

I am attempting to emulate a (no longer existing) mainframe report generator in an Access 2003 or Access 2010 environment. The data it generates must match exactly with paper reports from the early 70s. Unfortunately, the earliest years data were run on hardware that used IBM floating point representation instead of IEEE. With the help o...

How to Reference Individual Form Elements in a MS Access Continuous Form

MS Access Scenario: I am using a form (we'll call it the select_contract form) and a subform (we'll call it the employee_allocations_by_contract subform). The select_contract form: Is unbound. Contains a combobox that allows the user to select a contract id. The employee_allocations_by_contract subform: Is a continuous form....

How can I schedule a Macro to run automatically in Access 2007

I'd like to have a macro, called Macro1, for example, run every day at 9 AM. It works great on its own from the VB code editor in Access 2007 but I would like it to be able to execute automatically without access being open. Please note that I don't want there to have to be any human intervention, it needs to be able to run automaticall...

How to merge multiple excel files?

The whole idea is the following, I get monthly reports in excel files about large number of companies with couple of columns of data such as their closing share price for month, rating, p/e, and maybe one more column. Each company is sorted by a unique company ID, but from month to month the reports will contain about 70% records of same...

Database - Query

I have two Database (eg. two .mdb file). both have same name, same table and same field. the difference is one is old and other is updated copy of .mdb file. i want to copy only new records from the updated copy of the .mdb to the older one. is there any way other than iterate trough all the records and compare and filter only newer one?...

Creating a simple multi-user desktop application

I'm working on a simple mark management system for a school department (let's assume it's the Maths department). They want a simple system for users to enter marks for each student as they progress, entering marks for each term, getting yearly averages, sorting by teacher, etc. Speaking to the person in-charge of the department, he said...

Upgrade Path For Legacy Reporting ("Embedded" Access 2003)?

Update: Albert D. Kallal has kindly started the discussion off, and to get some more opinions I'm adding a bounty. This is a nontrivial question about maintenance of a legacy application myself and two other developers support. We are not the original developers, and the code base is 300,000 lines of MFC and business logic tightly co...

MS Access - check sub-form before entry for duplicate

Hi folks, I've got a subform (customersAnswersSub) inside of a main form (customersAnswers). Upon someone entering a new customerAnswersSub entry - I wanted it to check for duplicates first. It has to check across 4 different fields to match first. This is what I've got so far. Private Sub Form_BeforeUpdate(Cancel As Integer) Di...

problem getting my data from access 2003 db into c# (dbReader.GetString error)

[got a bit further so its updated] Hello There, i really hope you are able to help me! Now the first part of my code does work, and I do get my report numbers out in my combobox, and i'm able to write that number to a lbl. now I need to take that number and get the rest of my data from my Access 2003 database, and drop them in a strin...