Search results

Thank you, your suggestions worked. I was able to get the queries to run. I got pulled off of it for a little while and have just now got it fixed.

My first attempt at doing that was to copy the sql string into access and then manually add the second join, that's when I got the error messages. After you saying it should work I just wrote it into the string as: SELECT DISTINCT tblGeneral_InformationSecondary.P_Gen_Stock_Code AS [Stock...

I actually thought of that and when I try it I get an error message stating that the SQl statement cound not be executed becasue it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in...

Thanks for looking at this again. What I posted was only an example of a query that may be run. The reason all the tables are joined is that this sql is generated by users picking fields and parameters from and list and then combo box. Those fields and values are then joined to build the sql...

I had to add another table to this DB. It was working fine until some duplicate records were added to the DB. By duplicate, I mean that several revisions of the Stock_Code number have been added, e.g. originally all records were like : Stock Code Revision 0800-001 1 0800-005...

I will look over what you have provided and make another attempt. I mostly work on this at night after my day job. I do have vba experience, but mostly with Excel. Databases are relevatively new to me and the syntax examples I can find are not always that helpful. Again, thanks to you and...

I appreciate your help and am trying to pay attention. I'm trying to adapt what you guys are giving me to make it work with the initial code I provided. Your examples are not identical to what I'm trying to do and my experience with this is not as advanced as yours. I don't want to take up any...

jal - I was referring to Bob's example. I added a ref to DAO 3.6 (I think) that is the highest version I have using office 07 and it wouldn't recognize the format. I was able to get your version working but was unable to apply it using my additional parameters. This worked: Dim cn As New...

I understand what you're doing and I tried it but it doesn't work for my application. I'm using an access 07 database; I get the error message that the database format is unrecognized.

Sorry to be so dense, but this is all starting to run together. I need to be sure I understand this enough to attempt it. SELECT TableName.FieldName1, TableName.FieldName2 (this is in DB2) INTO SomeTableName (this is in DB1) FROM DB2 TableName (the same table as in the SELECT)

Hi Bob - I did try the two connection string approach, but had the same problems. I'll give your approach a try. I'm guessing the the Select syntax would be something like: SELECT Table1.Field1, Table1,Field2 INTO Table2 FROM Table1;

Bob - I'm doing this from an excel interface using ado and sql, so yes I'm outside both databases.

Thanks Bob; I'm trying to follow what you're doing. I see the OpenDatabase is supplying the password to the receiving database. But I'm not getting where the second database is getting it's password supplied? Sorry, I've been struggling with this on and off for days. I did get jal's code to...

Thanks Bob, in a case like what you're suggesting would the database you want to insert into be the current DB? Still how would you specify the passwords. I'm going to retry jal's suggestion and then perhaps bit the bullet and call Microsoft. $$$

Thanks David, but I don't believe that will work for our situation. The databases and interface are all on a shared team folder and everyone on the team would have the ability to open the databases. We need to password protect the databases for security, we can't afford to have any of the data...

jal - thank you for putting so much effort into this! I couldn't get this to work for me, could you please post a basic structure of the two databases you made? Perhaps I'm not getting the structures correct. e.g. DB1 Name = Names1 DB1 Table Name = Names1 DB1 Field1 = Names1_ID -...

I tried using two connections. I can see both of them open. I still get the error; invalid password. I know the passwords, I even tried several different passwords. I'm stumped, can't find much on line to help either.

Since the password is supplied in the connection string, could I have two connection strings? How would that work? Would I open the second database and then run the SQL through the primary connection? Not sure, but it's worth looking into.

thanks jal; no the initial connection string won't cover both of the databases. The password is supplied in the initial connection string that is the receiving database but the supplying database is also password protected. I think I tried that approach and it didn't work, but I'll look at it...

jal - thank you for taking the time to look at this. When I tried your suggestion it still doesn't work and the error description generated was: Characters found after end of the SQL Statement Any other thoughts?