Solved

Rewriting DAO query as ADO

Posted on 2004-09-05
12
1,219 Views
Last Modified: 2013-12-25
Does anyone happen to know what the ADO equivalent of this DAO query would be?

Database.Execute "SELECT name, address, address2, now as entry INTO [dBase 5.0;DATABASE=C:\DBF].[EXAMPLE] FROM [MyTable],[MyOtherTable] WHERE [MyTable].[id]=[MyOtherTable].[id] AND NOT [MyTable].[Imported]"

This is just a sample, as the actually query is about 7 lines long, but it includes all the elements of the original.  I've tried many things, but just one error after another.  Many thanks and 250 points to the first person that can help solve this problem!
   
0
Comment
Question by:sloppyjoe73
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 5

Expert Comment

by:Z03niE
ID: 11987086
SELECT name, address, address2, now
INTO [EXAMPLE]
FROM [MyTable]
    JOIN [MyOtherTable]
            ON [MyTable].[id]=[MyOtherTable].[id]
            AND NOT [MyTable].[Imported]  --what is this line for ? The field is not compared with any value.

Are your trying to insert some values from a database into another ?
I think it'll be better if you declare which fields you want to insert the value into.Like :

INTO [EXAMPLE] (name, address, address2, now)

I noticed that you are using Dbase 5. I don't know if dbase recognise this query. Why don't you convert the database to Access ? I guess Access is better than Dbase 5.
0
 
LVL 1

Author Comment

by:sloppyjoe73
ID: 11987119
Actually, I should have called it [MyTable].[Exported]

[MyTable].[Exported] is a boolean (yes/no as it would be called in Access) value.  Once the record gets exported into the DBF, the "exported" field gets set to true so that the same record isn't processed again.  The program keeps a short history of records in case one needs to be resubmitted in the future.

I'm actually going through the query now removing the fields one at a time.  It seems to work fine when I remove all but the first few fields.  Maybe it isn't the syntax after all.. just a problem with one of the fields.  Gonna go check my fields.
0
 
LVL 1

Author Comment

by:sloppyjoe73
ID: 11987365
Looks like it's being strange with some of the fields.  Since I'm not in control of the program on the other end of this DBF file (written in FoxPro), I'm having to insert some empty fields into the dbf as to not make the program on the receiving end cranky.  I'd much rather just leave them out, but the program on the other end is expecting them to be there.  I've been using syntax such as:

SELECT normalfield, '' as blankstringfield, 0 as blanknumericfield

For some of the fields this works just fine, but for others it gives me the error "Field will not fit in record."  If I use a blank field from the access database in place of '', it works just fine.  i.e.

SELECT normalfield, blankDBfield as blankstringfield, 0 as blanknumericfield

I would think it would see '' (two single quotes) as a zero-length string, but for some reason it thinks that won't fit in the target field...  Any ideas as to why it would behave this way?  I can't say I know much about DBF files, or putting data into them for that matter.
0
 
LVL 5

Expert Comment

by:Z03niE
ID: 11987498
Neither do I. Too bad.
I guess we'll have to wait for other member who might know more about dbf.
Or maybe you may try another method, why don't you try inserting using recordset.
Something like this :

Dim Rs,Rs1 as adodb.recordset
set rs = new adodb.recordset
rs.open " SELECT name, address, address2, now FROM [MyTable] JOIN [MyOtherTable] ON [MyTable].[id]=[MyOtherTable].[id]  AND NOT [MyTable].[Imported]" '.... blah... blah like you open usual ADO recordset
rs1.open "select name, address, address2, now from [EXAMPLE]" '... blah... blah too.. connection, cursor and stuff

rs1.addnew
rs1.fields(0) = rs.fields(0)
rs1.fields(1) = rs.fields(1)
rs1.fields(2) = rs.fields(2)
rs.update


Maybe that onei'll wotk. Cause when recordset update a table, it create empty row first and then fill in the empty row with data. Just hope so.
0
 
LVL 1

Author Comment

by:sloppyjoe73
ID: 11987692
I suppose that's my next option.  I'm kinda putting it off because there are so many fields.  I mean, I like typing and all, but still....  my example was rather abbreviated.  Although I suppose with as much time as I have spent on this I could have typed it out that way by now.

I'm wondering if I just hit some sort of upper limit on record size, because looking at the DBF files I'm creating, all the places I used   '' as fieldname    it assigns it a field length of 254.  After so many empty fields like that, the record would get rather large in theoretical length.  When I assign it a blank Access DB field with a set length of 1, it has no problem.   Hmmmm.....
0
 
LVL 32

Accepted Solution

by:
jadedata earned 125 total points
ID: 11991888
Greetings sloppyjoe73!

  As long as you do not fill those fields to capacity you will get no arguement from Access.
  Have you considered having the Target table already built as a template to catch records from an append query?
  or using a table of field specs to build the table on the fly?

regards
:)-j-
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:leonstryker
ID: 11997495
Just to expand on jadedata  recommendation.  Use ADOX to create the tables and then insert records into them.

Leon
0
 
LVL 32

Expert Comment

by:jadedata
ID: 11997853
Frequently the "special" properties of fields that you like are not available in the ADO or ADOX object models.
But all object models can be copied and used as a new empty container.
0
 
LVL 1

Author Comment

by:sloppyjoe73
ID: 11999748
I think I would have to use a table of field specs to build the table on the fly, since the name of the table is created dynamically by the program.  I'm not exactly sure how to do this, however, as databases are decidedly not my specialty.  Anyone have any sample code I might use as a starting point?
0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 125 total points
ID: 12000425
Here are examples as to how to create databases objects on the fly with ADOX:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscadoxcodeexamplesvb.asp

Leon
0
 
LVL 1

Author Comment

by:sloppyjoe73
ID: 12016839
I think I can take it from here.

A big thank you to everyone that helped me with this problem!
0
 
LVL 32

Expert Comment

by:jadedata
ID: 12017130
you're very welcome!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Use closed file on desktop in vba 6 65
Access 2016 VB code 9 102
VBA open file from excel cell 4 36
maro to copy and paste from one worksheet to another based on a condition 11 47
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now