Accessing a DB with VBS?

Anyone here who can please show me with some vbs code how to write, read,
change and delete data to/from a database?

Thank you.

Noel.

Noël
Tue Jul 08 09:46:59 CDT 2003

| > Anyone here who can please show me with some vbs code how to write,
read,
| > change and delete data to/from a database?
| >

|
| What kind of database?
|

Yes, I should have mentioned it.

It's a simple .mdb file created with Access 2002

Noel.

Ray
Tue Jul 08 10:02:32 CDT 2003

C:\file.mdb
Table1:
ID Firstname
1 Jorg

--------------------------------

Dim objADO, sSQL
Dim rsUser
sSQL = "SELECT [Firstname] FROM [Table1] WHERE [ID]=1;"

Set objADO = CreateObject("ADODB.Connection")
objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\file.mdb;"
Set rsUser = objADO.Execute(sSQL)
If Not rsUser.EOF Then
Msgbox "Your name is " & rsUser.Fields.Item(0).Value
Else
Msgbox "A user with that ID number was not found."
End If
rsUser.Close
Set rsUser = Nothing
objADO.Close
Set objADO = Nothing

---------------------------------

See connection strings for different DBs at
http://www.connectionstrings.com/ (which is looking rather odd today...).

Ray at work






"Noël" <n03l@hotmail.com> wrote in message
news:Oj$wb%23VRDHA.212@TK2MSFTNGP10.phx.gbl...
> | > Anyone here who can please show me with some vbs code how to write,
> read,
> | > change and delete data to/from a database?
> | >
>
> |
> | What kind of database?
> |
>
> Yes, I should have mentioned it.
>
> It's a simple .mdb file created with Access 2002
>
> Noel.
>
>

Ray
Tue Jul 08 12:16:45 CDT 2003

Yeah, the connection strings site is just for connection strings. I don't
think there's any SQL examples. They're all over the place though.
Basically, what you do is create an ADOBC.Connection and execute a SQL
command. But what are the SQL commands? You have INSERT, DELETE, UPDATE,
SELECT as the basics. Here are some sample SQL commands:


INSERT INTO [Table1] (Column1,Column2) VALUES (3, 'A word');

DELETE FROM [Table1] WHERE [Column9]='a value';

UPDATE [Table1] SET [Column1]=5 WHERE [Column1]=50

Don't do the "Set NameOfRecordSet=objADO.Execute(sSQL)" when inserting,
deleting, or updating. Just do:


Set objADO = CreateObject("ADODB.Connection")
objADO.Open YourConnectionString
objADO.Execute sSQL
objADO.Close
Set objADO = Nothing


One way to help yourself learn some querying is to design the query in
Access, if you're familiar with Access, and then switch to SQL view to see
the SQL it generated.

Ray at work


"Noël" <n03l@hotmail.com> wrote in message
news:%23ROHHCXRDHA.2432@TK2MSFTNGP10.phx.gbl...
> |
>
> Thank you Ray, reading from the DB is working great! Now I need to figer
out
> a way how to write "Ray" to the DB.
> I've had a look at http://www.connectionstrings.com , but did not find a
> way to do this part (yet).
>
> Noel.
>
>

Noël
Tue Jul 08 13:05:41 CDT 2003


| Yeah, the connection strings site is just for connection strings. I don't
| think there's any SQL examples. They're all over the place though.
| Basically, what you do is create an ADOBC.Connection and execute a SQL
| command. But what are the SQL commands? You have INSERT, DELETE, UPDATE,
| SELECT as the basics. Here are some sample SQL commands:
|
|
| INSERT INTO [Table1] (Column1,Column2) VALUES (3, 'A word');
|
| DELETE FROM [Table1] WHERE [Column9]='a value';
|
| UPDATE [Table1] SET [Column1]=5 WHERE [Column1]=50
|
| Don't do the "Set NameOfRecordSet=objADO.Execute(sSQL)" when inserting,
| deleting, or updating. Just do:
|
|
| Set objADO = CreateObject("ADODB.Connection")
| objADO.Open YourConnectionString
| objADO.Execute sSQL
| objADO.Close
| Set objADO = Nothing
|
|
| One way to help yourself learn some querying is to design the query in
| Access, if you're familiar with Access, and then switch to SQL view to see
| the SQL it generated.
|

Thanks again Ray, you are the man!

I'm trying the following code to INSERT, but as usual it's not working..

Dim objADO, sSQL
Dim rsUser
sSQL = "INSERT INTO [Table1] (Column1,Column2) VALUES (3, 'A word');"

Set objADO = CreateObject("ADODB.Connection")

objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\file.mdb;"
odjADO.Execute sSQL
objADO.Close

Set objADO = Nothing

There is a DB name file.mdb at C:\ but the error I get is "Object required:
odjADO"
Did I miss something?

The reading part from the DB (with the code you gave me) is working fine!
Should I change the "Column1" to "ID" and the "Column2" to "Firstname" (as
shown in your read-demo-code)?

Noel.

Noël
Tue Jul 08 13:30:45 CDT 2003


| > | Yeah, the connection strings site is just for connection strings. I
| don't
| > | think there's any SQL examples. They're all over the place though.
| > | Basically, what you do is create an ADOBC.Connection and execute a SQL
| > | command. But what are the SQL commands? You have INSERT, DELETE,
| UPDATE,
| > | SELECT as the basics. Here are some sample SQL commands:
| > |
| > |
| > | INSERT INTO [Table1] (Column1,Column2) VALUES (3, 'A word');
| > |
| > | DELETE FROM [Table1] WHERE [Column9]='a value';
| > |
| > | UPDATE [Table1] SET [Column1]=5 WHERE [Column1]=50
| > |
| > | Don't do the "Set NameOfRecordSet=objADO.Execute(sSQL)" when
inserting,
| > | deleting, or updating. Just do:
| > |
| > |
| > | Set objADO = CreateObject("ADODB.Connection")
| > | objADO.Open YourConnectionString
| > | objADO.Execute sSQL
| > | objADO.Close
| > | Set objADO = Nothing
| > |
| > |
| > | One way to help yourself learn some querying is to design the query in
| > | Access, if you're familiar with Access, and then switch to SQL view to
| see
| > | the SQL it generated.
| > |
| >
| > Thanks again Ray, you are the man!
| >
| > I'm trying the following code to INSERT, but as usual it's not working..
| >
| > Dim objADO, sSQL
| > Dim rsUser
| > sSQL = "INSERT INTO [Table1] (Column1,Column2) VALUES (3, 'A word');"
| >
| > Set objADO = CreateObject("ADODB.Connection")
| >
| > objADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\file.mdb;"
| > odjADO.Execute sSQL
| > objADO.Close
| >
| > Set objADO = Nothing
| >
| > There is a DB name file.mdb at C:\ but the error I get is "Object
| required:
| > odjADO"
| > Did I miss something?
| >
| > The reading part from the DB (with the code you gave me) is working
fine!
| > Should I change the "Column1" to "ID" and the "Column2" to "Firstname"
(as
| > shown in your read-demo-code)?
| >
| > Noel.
| >
| >
|
| Use Option Explicit and you'll see your error. Force yourself into this
| habit, man! :]
|

Hehe.. Thanks Ray, I found the error.. Guess what.. a typo.
odjADO.Execute sSQL should be objADO.Execute sSQL (note the d and the b)

Thank you again for all your help! Give me your address and I will send you
some wine!

Have a nice day!

Noel.
PS. Thanks for the Option Explicit tip.. Will do so from now on..

Ray
Tue Jul 08 13:48:13 CDT 2003

Heh heh heh. No problem. And I don't need any wine, but if you come to PA,
I'll let you buy me a beer. :] I think that the movie, "Pay it Forward"
was written by someone who learned about a technology in a newsgroup, since
that's what the newsgroups are like.

Ray at work

"Noël" <n03l@hotmail.com> wrote in message
news:u2Pue7XRDHA.2408@TK2MSFTNGP10.phx.gbl...
>
> Hehe.. Thanks Ray, I found the error.. Guess what.. a typo.
> odjADO.Execute sSQL should be objADO.Execute sSQL (note the d and the b)
>
> Thank you again for all your help! Give me your address and I will send
you
> some wine!
>
> Have a nice day!
>
> Noel.
> PS. Thanks for the Option Explicit tip.. Will do so from now on..
>
>

Michael
Tue Jul 08 14:58:40 CDT 2003

Noel,

Check out www.asp101.com and go to the samples page. There are samples on
how to do this in both Access and SQL.

Mike
"Noël" <n03l@hotmail.com> wrote in message
news:OtLXYaURDHA.3880@tk2msftngp13.phx.gbl...
> Anyone here who can please show me with some vbs code how to write, read,
> change and delete data to/from a database?
>
> Thank you.
>
> Noel.
>
>