Search results

Well, I haven't been testing my code. Here's a correction (also untested). SELECT F.* INTO KeepThese2 FROM [The Final Query] as F INNER JOIN ( SELECT DISTINCT F.names_1 FROM [The Final Query] as F LEFT JOIN ( SELECT names_1 FROM [The Final Query] as F...

By the way, the reason you dont have to add a ref in Access 2007 as that it presumes the new DAO to be the default DAO.

Ok, for the new DAO compatible with accdb, add a ref to Microsoft Office 12.0 Access DBengine Object library. The syntax is mostly the same as I seem to recall.

You probably are using DAO 3.6. There is a new version, you will have to add a reference to it (probably at Tools > References in Excel). I'll see if I can get you more info on this. Also, I think I'll try to produce an .accdb version of my ADO code for you to see. I don't have Access 2007...

. I'm at work (the data is at home) but in both DBs the tables were identical (same names). "Names" is a reserved word, I guess, so maybe you can call it AllNames, like this (there was no autonum or pk col) - just one col called Name. e.g. DB1 Name = Names1.mdb DB1 Table Name = AllNames DB1...

This also worked (this time I insert into the first db): Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=access;Data Source=C:\Names1.mdb" cn.Open Dim cmd As New ADODB.Command cmd.ActiveConnection = cn...

I got it working on my machine. Here's the code: Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Database Password=access;Data Source=C:\Names1.mdb" cn.Open Dim cmd As New ADODB.Command cmd.ActiveConnection = cn...

I tried it with .Mdb files and got a similar error. This sort of thing seems to run into a problem when passwords are involved. This thread proposes a solution but it doesn't seem to work for me. INSERT INTO [;DATABASE=d:\Apppath\local.mdb;PWD=tej].InvoiceItems SELECT InvoiceItems.* FROM...

Try again to let the one connection string suffice for the two databases. But you first need to fix something in your code. You specified two paths. But you should only need to specify one path. INSERT INTO...

It shouldn't be SELECT ID - it should be SELECT names_1. Sorry. Try this: SELECT F.* INTO KeepThese1 FROM [The Final Query] as F INNER JOIN ( SELECT names_1 FROM [The Final Query] as F GROUP BY names_1 HAVING COUNT(*) >=6 ) as NamesToKeep ON NamesToKeep.names_1 = F.names_1...

Yeah, that was a typical dumb oversight on my part. I'll have to fix that.

Unless I'm not thinking clearly, I suppose all we need to do, then, is to remove the MIN(ID) restriction (change it to SELECT ID). First query: SELECT F.* INTO KeepThese1 FROM [The Final Query] as F INNER JOIN ( SELECT ID FROM [The Final Query] as F GROUP BY names_1 HAVING COUNT(*) >=6 ) as...

You want all 6 of Jen in one table, then, right? KeepThese1 And you want all the Harrys and Donalds in another table, right? KeepThese2 Or do I still have it wrong?

Certainly I've tried this without passwords, and the initial connection string sufficed for all the (.MDB) databases. About that I have no doubt, as I've got a couple of programs using that method right now (written in C#.Net).

Re-reading your code, you seem to be sharing the same password accross the two databases. Doesn't the initial connection string suffice, then?

Oh, sorry, I just remembered that passwords are usually specified in the connection string, not in the SQL. I'm an idiot. And I have no experience with passwords. I'm guessing, if the two databases have the same password, the initial connection string should suffice (i.e. there should be no...

Your ending the quotation mark at the wrong place. It should be; IN 'D:\RMS Database System\Raw Material Specification\Secondary RMS Database\ aw Material Specification Secondary Database.accdb';PWD=roscoe;

As far as I can see, the SQL I wrote is just fine. The problem is that you keep changing your request. Here's what you said last time: That's precisely what I did, as far as I can see. Now you're changing things again - this time it's "more than 6". Make up your mind.

Come to think of it, I'm confused by this line: strWhere = Mid(strWhere, 1, Len(strWhere) - 5) I would have expected "4" intead of "5", but I'm probably missing something here.

Out of curiosity exactly how large are these "huge" tables? And exactly how long is the "significant delay"? Without some kind of ballpark figures,it's hard for us to know how severe the problem is.