Re: VB6 Change all records in specific table

Tech-Archive recommends: Fix windows errors by optimizing your registry

You need to learn how to use SQL.

The SQL statement for what you're trying to do would be:

UPDATE Links SET [Type] = "R" WHERE [Type] = "T"

Note the inclusion of the square brackets around Type: Type is a reserved
word, and really shouldn't be used for your own purposes. If you cannot (or
will not) rename the field, using the square brackets will lessen the
likelihood of problems.

Unfortunately, I'm not sure how you use a DSN connection to connect to an
Access database with VB6: I've always just used DAO when working with Access
and VB, so I'd instantiate a Database object, and run the SQL:

Dim dbCurr As DAO.Database
Dim strSQL As String

strSQL = "UPDATE Links SET [Type] = 'R' WHERE [Type] = 'T'"

Set dbCurr = OpenDatabase("PathToMDB")
dbCurr.Execute strSQL, dbFailOnError


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Josh" <hype8912@xxxxxxxxx> wrote in message
news:1168152426.165159.85690@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am pretty good with VB, but I don't understand how to use databases
with VB yet. I prefer to use VB6 over 2003 or 2005 because I don't have
to worry about the bloated permissions.

I am trying to access an MS Access 97 database called "DB.mdb" with
multiple tables in it. In the "Links" table, field called "Type" I need
to change all the "T"s to "R"s. This field only contains one letter at
all times. There are over 300 thousand records in this table.

I usually do the replacement of the Ts to Rs manually but I would like
to programmatically do this from a single script.

I have a System DSN connection already setup called "DBConn" pointing
to the access database.
The user name is "admin" and the password is "db0102"

How do I setup a connection in VB6 to use the DSN connection already
setup to change all the records in the specific table field? I would to
prefer to see code as thats how I've learned anything so far.

Any books or links would be useful too.



.

Relevant Pages

  • Re: VB or?
    ... I know you can use an Access database with it, but it's not the standard, instead using compact SQL for the desktop, or the free version of SQL would be the more "in the box" approaches. ... If you haven't been programming for 12 years, then I'd suggest try the .NET version and see how you go. ... You can buy a .NET standard version and downgrade I believe, but not sure on that status now as officially VB6 is no longer a supported product. ...
    (microsoft.public.vb.general.discussion)
  • Re: Anyone tell me whats wrong with this SQL statement?
    ... That sounds like an SQL error telling you that you are not accounting ... am also working on a Photo Album which uses an Access ... > PS - just to cover every area, the data types in Access database are as ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: VB or?
    ... I know you can use an Access database with it, but it's not the standard, instead using compact SQL for the desktop, or the free version of SQL would be the more "in the box" approaches. ... If you haven't been programming for 12 years, then I'd suggest try the .NET version and see how you go. ... You can buy a .NET standard version and downgrade I believe, but not sure on that status now as officially VB6 is no longer a supported product. ...
    (microsoft.public.vb.general.discussion)
  • Re: Calling SQL Stored Proc from Access (with Parameters)
    ... You can put the code in the after update of your "form field". ... property of the pass-through query. ... I have an access database and a SQL database. ...
    (microsoft.public.access.queries)
  • Re: Inconsistent square bracket in Query SQL
    ... Perhaps this is the redefinition by ACCESS of the subquery to the ... > Access will add square brackets around a field name that requires it, so I am wondering whether Name> AutoCorrect thinks it's safer to add the square brackets. ... After turning this> off, compact the database, then open the query in SQL View, change the text> and save. ... The only> issue I've seen with comparing the SQL of the QueryDef is that it tends to> have a trailing CrLf. ...
    (microsoft.public.access.queries)