How to bind variables

  • Thread starter Thread starter everymn
  • Start date Start date
E

everymn

Hi,

I'm trying to run some ADO/ODBC tests. Trying to read a table, and
copy those records to a new table. To do this I've been parsing the
retruned records and building an INSERT statement, but that is proving
to be increadibly slow because of the parsing, and all of the
operations necessary to determine the type of each field and reformat
each value to that it can be re-inserted into a new table.

There has to be a simpler way to do this right in ADO no? Someone
mentioned that reading into bound variables might help, but I can find
any articles that describe binding unless it's binding to a form
control, which isn't really needed or desired.

If anyone could describe briefly what I need to do, or point me to a
description of it that would be very helpful

Thank You.
 
I'm pretty sure they meant binding to a form control. To my knowledge, there
is no such thing as binding a variable. Without reviewing your code, I have
no idea if there is a simpler way.
 
I'm pretty sure they meant binding to a form control. To my knowledge, there
is no such thing as binding a variable. Without reviewing your code, I have
no idea if there is a simpler way.
Click to expand...

In pseudo

while not recordset.eof
for each field in recordset.fields
dim allValues as string
parse the field value and '\' everything
that needs to be escaped
take that new value and if it's a date, reformat
it so that it can be reinserted into a new table
else if it is a string, put single quotes around it
in case there are blank spaces in the string
append the reformatted value to the end of
the allValues string
loop
insert into newTable values (allValues)
wend

To me this seems awfully roundabout. I would have thought there would
be some way to take a returned record set and simply insert that into
a different table.

I could off course issue SQL statements to copy the table inside the
database but that misses the point of the exercise which is to test
how ADO/ODBC works with this db
 
The first thing that pops out is where you have your Dim statement. It is
inside the loop. Although others will argue against it, my practice is to
put all Dim statements at the beginning of the procedure or for module level
variables, at the top of the module.

It appears you are using one recordset to create an SQL INSERT statement.
If that is correct, your syntax is not quite correct. The field delimiter
should be a , not \.

Is there any reason you are not using this method:

Do While Not RecordsetOld.EOF
RecordsetNew.AddNew
RecordsetNew![Field1] = RecordsetOld![Field1]
RecordsetNew![Field2] = RecordsetOld![Field5]
RecordsetNew![Field3] = RecordsetOld![Field2]
RecordsetNew.Update
RecordsetOld.MoveNext
Loop
 
Trying to read a table, and
copy those records to a new table.
Click to expand...

INSERT INTO NewTable(FieldOne, FieldTwo)
SELECT ALL Field1, Field2 FROM OldTable

or

SELECT ALL Field1, Field2
INTO NewTable
FROM OldTable

Either of these will run under DAO or ADO methods.

Hope that helps


Tim F
 
The first thing that pops out is where you have your Dim statement. It is
inside the loop. Although others will argue against it, my practice is to
put all Dim statements at the beginning of the procedure or for module level
variables, at the top of the module.
Click to expand...

My bad, I declare that string outside the loop. This is where I'm
building the string of values to be used in the insert statement.
It appears you are using one recordset to create an SQL INSERT statement.
If that is correct, your syntax is not quite correct. The field delimiter
should be a , not \.
Click to expand...

No the \ is being inserted to escape characters that need to be
escaped when they are found in strings, namely another \, or a single
quote. As I get each value from each field, I cat a comma & the value
to the end of the allValues string. If I don't escape them the
database kicks back the INSERT.
Is there any reason you are not using this method:

Do While Not RecordsetOld.EOF
RecordsetNew.AddNew
RecordsetNew![Field1] = RecordsetOld![Field1]
RecordsetNew![Field2] = RecordsetOld![Field5]
RecordsetNew![Field3] = RecordsetOld![Field2]
RecordsetNew.Update
RecordsetOld.MoveNext
Loop
Click to expand...

Ah okay now that seems like something I need to think about. So you
are building a new record set based on the values of the old set
right. Why set field 2=field5?

When I call AddNew on the new recordSet I'm adding a new Record to the
set right? Do I need to add fields to the new Record or does making
an assignment to a field as you are doing call that field into
existance?

So assuming I want this RecordSetNew to add records that have been
read from table1, into table 2. How would I have initialized
recordSet2, and what exactly is going to happen when I call update?
Thanks for the Help
 
Back
Top