Updating Multiple Fields 
Author Message
 Updating Multiple Fields

Is your "Access" a variable of type database?  Or are you using for a generic
Access descriptor?

In either case, a better way to update one table in one database with data from
a table in another database is to define the two tables as recordsets and use a
Seek or FindFirst method in the source table and then updating the target
table.

Robert Hurlbut
VB Consultant


says...

Quote:

>To Anyone,

>I am currently connected to 2 DB's.  I am trying to have on DB update the
>Password field if it matches the user id of the other DB.  If the User ID
>matches, I would like the second DB to update the information... Here is
>some of the code:

>        Access.Execute "UPDATE customers " _
>            & "SET customer_id = (DataFlexRecords!ID)" _
>            & "SET password= (DataFlexRecords!PASSWORD)" _
>            & "WHERE customer_id= (DataFlexRecords!ID);"

>This is where I am getting a syntax error.  Does anyone know what I am
>doing wrong?

>Thanks in advance,

>Marc Brown



Tue, 01 Feb 2000 03:00:00 GMT  
 Updating Multiple Fields

Hey Marc,

I see several problems here.  First, a problem that most beginners have and
that I had too, is that program variables must be appended to the sql
statement instead of just thrown inside of the double quotes.  Second is
that if a field is of type text or string or varchar or whatever means
alphanumeric characters to your database, then the data to store in that
field must be surrounded by single quotes.  The single quotes, being part
of the string and not the variable, must always be inside the double quotes
instead of directly around the variable.  Third, replace the second SET
with a comma.  Fourth, you need an extra space at the end of lines 2 and 3
or at the beginning of lines 3 and 4.  And finally, if this is an Access
database, I believe you need to get rid of the semicolon at the end.  This
is how I would do it:

sql$ = "UPDATE customers" & _
          " SET customer_id = '" & DataFlexRecords!ID & "'," & _
          "        password = '" & DataFlexRecords!PASSWORD & "'" & _
          " WHERE customer_id = '" & DataFlexRecords!ID & "'"
Access.Execute sql$

Of course this assumes that customer_id is text.  If it is defined as
Integer or Long or Double or any other numeric type, then remove the single
quotes in the lines that contain customer_id like this:

          " WHERE customer_id = " & DataFlexRecords!ID

One other thing I just noticed.  Since you are searching for customer_id =
DataFlexRecords!ID, there is no reason to update that customer_id to the
same value.  So this would be better:

sql$ = "UPDATE customers" & _
          " SET password = '" & DataFlexRecords!PASSWORD & "'" & _
          " WHERE customer_id = '" & DataFlexRecords!ID & "'"
Access.Execute sql$

Good Luck
--
Lance King
Work: 770-801-4502

Take out "nospam." to send message.



Quote:
> To Anyone,

> I am currently connected to 2 DB's.  I am trying to have on DB update the
> Password field if it matches the user id of the other DB.  If the User ID
> matches, I would like the second DB to update the information... Here is
> some of the code:

>    Access.Execute "UPDATE customers " _
>             & "SET customer_id = (DataFlexRecords!ID)" _
>             & "SET password= (DataFlexRecords!PASSWORD)" _
>             & "WHERE customer_id= (DataFlexRecords!ID);"

> This is where I am getting a syntax error.  Does anyone know what I am
> doing wrong?

> Thanks in advance,

> Marc Brown



Tue, 01 Feb 2000 03:00:00 GMT  
 Updating Multiple Fields

To Anyone,

I am currently connected to 2 DB's.  I am trying to have on DB update the
Password field if it matches the user id of the other DB.  If the User ID
matches, I would like the second DB to update the information... Here is
some of the code:

        Access.Execute "UPDATE customers " _
            & "SET customer_id = (DataFlexRecords!ID)" _
            & "SET password= (DataFlexRecords!PASSWORD)" _
            & "WHERE customer_id= (DataFlexRecords!ID);"

This is where I am getting a syntax error.  Does anyone know what I am
doing wrong?

Thanks in advance,

Marc Brown



Tue, 01 Feb 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. updating multiple fields in recordset??

2. updating multiple fields in recordset??

3. How do I update multiple field ?

4. updating multiple fields in recordset??

5. single field to multiple field update qry

6. Single field into multiple fields

7. Parsing a Field into Multiple Fields

8. populate multiple fields based on input keyed into one field

9. Select distinct on 1 field but returns multiple fields

10. Creating multiple fields from one concatinated field

11. Using--After Update-- to update field in table.

12. Updating fields: footer doesn't update right

 

 
Powered by phpBB® Forum Software