Search results

I feel like a complete idiot. I used to be able to write simple queries but I haven't had much practice lately and now it seems I've completely forgotten how. If you type SELECT 100/20 in a query window it will return "5" (try it for yourself), and in that same vein I want to run SELECT...

A while back I finally became aware that AutoNumber columns do not necessarily provide unique values. Today I discovered a bug in an old database. I had a query like this (note that tblCombine is initially empty at the start of this query). INSERT INTO tblCombine ( SELECT * FROM table1...

I have a table with a column called Frequency (numeric). The user wants to do a simple search, for example, WHERE Frequency BETWEEN 500 AND 600 The table is very large (4 million records) and I have to do some joins. I found out, the hard way, that the search runs 10 times faster if the...

Apparently there is a design issue which I don't understand. I have a table with an Account column (account number). It's of type TEXT and length 50. For some reason Jet has it as a fixed-length column. How do I undo that? That is to say, even if I insert a 10-digit account number, Jet is...

I'm using C#.Net to populate an .MDB database, and invoke the DAO 3.6 library from C# to compact the file nightly. I'm now having a performance issue, and I'm wondering if the nightly compacting is what triggered it. (I can't remember when I first began the nightly compacting). I've deleted...

See attached query. The inner SELECT works fine as a standalone query, but when wrapped in the outer SELECT, I get the error "An action query cannot be used as a row source." I'm running this query on an MDB file from C#.Net, but I get the same results if I copy and paste it into Access 2003...

Does Jet do joins efficiently? I have a Jet database packed with 10 million records (account numbers) ,and I'm very impressed how fast a user can look up an account number. On the other hand I was surprised by the following scenario. I have two tables (let's call them Customers and Orders to...

This should be a simple query (this is a one-table query with no joins), but I'm getting "DataType Mismatch in Query expression." Here's the deal. Our account numbers are generally 13 digits long. The first three digits constitute the facility number (FAC). All I'm doing is extracting the first...

I can't figure out how to open an .MDB file in exclusive mode using C#. For example I've tried this: object oMissing = System.Reflection.Missing.Value; DAO.DBEngine eng = new DAO.DBEngine(); DB = eng.OpenDatabase(pathToDB, oMissing, true,oMissing);

At my job each computer has read-only access to the Cdrive of the other computers. So here's the problem. I need to store an Access .MDB file on one of the computers (for read-only access). About ten users will connect to it (probably no more than two or three at a time). With DAO I couldn't...

I have a bit of Access experience but am new to threading. I'm using C#.Net to load millions of records into an Access DB. I spun off a thread as to give the user the option to cancel the operation and resume later. Actually I'm using OLEDB to populate the DB. However, I'm not aware of a way...

I'm using C#.Net code to populate an .MDB file (with data pulled from textfiles). This is my first experience with a large table. After populating half a million records my C#.Net program called "SearchText" closes with a message, "SearchText has encountered a problem and has to close. We are...

Suppose a student owns a student-version of Access. Excited about his latest class project, he decides to share his Access VBA code on the web. So he uploads an .mdb file or .accdb file to his website. As a result, numerous programmers download his project and use it in their business...

I have an Access 2003 db (.mdb file) that the users will connect to only on a read-only basis. I will compact it before installing it on their machines. If they only run Select queries on it, will the database expand?

I'm fairly new to Access 2007. In SQL view when I hit F5 to run a query (as we did in Access 2003) it seems to ignore me. So on the menu I've been going to Design > Results > Run and clicking the red exclaimation point labled "Run". I had some luck doing this (as I recall) last week but...

If I write a query like this: UPDATE Customers SET FirstName = UCase(FirstName), LastName = UCase(LastName) and then save it in Access 2007, it prefixes the table name: UPDATE Customers SET Customers.FirstName = UCase(Customers.FirstName), Customers.LastName = UCase(Customers.LastName) I...

Excel 2007, .XLSX file. If I issue a Create Table sql command from VBA using an Excel connection string CREATE TABLE Customers I end up with a worksheet named "Customers" (instead of Sheet1). Perfect. However, the problem is that I now have a worksheet that I renamed manually (using my...

I tried: ALTER TABLE groupids ADD COLUMN groupid TEXT But when I poll for the datatype I get "Memo". This datatype will cause me problems because Jet won't let me inner join on Memo fields. I need TEXT field. I tried "Alter Column GroupID Text" but it remains Memo (no error throws). I am...

I'm using vbscript which is almost identical to VBA. I did the hard part, I read in a textfile, parsed it, and inserted the words and numbers into Excel. But it's the "easy" part that's killing me. One of the columns which I am parsing has spaces prepended, such as " John Smith" I...

I've got two identical spreadsheets (same column names and datatypes) filled with patient names - the only difference is that it's two different sets of patient names. 60 rows in each sheet. I just want one sheet with 120 rows. When I try to copy and paste using my mouse, the dest sheet gets...