Insert into a database

[OdAwG]

Hello All,

Given the following code from several differenet sources within this
newsgroup, we were able to come up with a way to connect to a database and
retrieve data from it. The code is listed below:

(******************************************)
01. Dim MyConn, rs, sql
02. Set MyConn = CreateObject("ADODB.Connection")
03.
04. MyConn.Provider = "Microsoft.Jet.OLEDB.4.0"
05. MyConn.Properties("Data Source") = "\\server\share\APPS_DB.mdb"
06. MyConn.Open
07.
08. Set rs=CreateObject("ADODB.recordset")
09. sql = "SELECT * FROM tbl_web_app"
10. rs.Open sql, MyConn
11.
12. Dim ID, Pwd, Account
13.
14. user_ID = (rs("ID"))
15. user_Pwd = (rs("Pwd"))
16. user_Account = (rs("Account"))
17.
18. some other work related code blah blah blah
19.
20. rs.Close
21. Set MyConn = Nothing
22. Set rs = Nothing

(*******************************************)

Now, what I am wondering is if there is a way using the code above to insert
data into the tbl_web_app in the APPS_DB.mdb file? I tried the following
but it doesn't work; nothing gets inserted into the database . I replaced
the following:

line 09. with sql = "INSERT INTO tbl_web_app (ID, Pwd, Account) Values
('user_ID', 'user_Pwd', 'user_Account')"

I also removed lines11 - 17. I don't think I need them for the INSERT part.
we have some other code before the database connection that sets the
variables for user_id, user_Pwd, and user_Account.

Any and all help in this matter is greatly appreciated,

Argus

[Randy Harris]

* OdAwG:

Hello All,

Given the following code from several differenet sources within this
newsgroup, we were able to come up with a way to connect to a database and
retrieve data from it. The code is listed below:

(******************************************)
01. Dim MyConn, rs, sql
02. Set MyConn = CreateObject("ADODB.Connection")
03.
04. MyConn.Provider = "Microsoft.Jet.OLEDB.4.0"
05. MyConn.Properties("Data Source") = "\\server\share\APPS_DB.mdb"
06. MyConn.Open
07.
08. Set rs=CreateObject("ADODB.recordset")
09. sql = "SELECT * FROM tbl_web_app"
10. rs.Open sql, MyConn
11.
12. Dim ID, Pwd, Account
13.
14. user_ID = (rs("ID"))
15. user_Pwd = (rs("Pwd"))
16. user_Account = (rs("Account"))
17.
18. some other work related code blah blah blah
19.
20. rs.Close
21. Set MyConn = Nothing
22. Set rs = Nothing

(*******************************************)

Now, what I am wondering is if there is a way using the code above to insert
data into the tbl_web_app in the APPS_DB.mdb file? I tried the following
but it doesn't work; nothing gets inserted into the database . I replaced
the following:

line 09. with sql = "INSERT INTO tbl_web_app (ID, Pwd, Account) Values
('user_ID', 'user_Pwd', 'user_Account')"

I also removed lines11 - 17. I don't think I need them for the INSERT part.
we have some other code before the database connection that sets the
variables for user_id, user_Pwd, and user_Account.

Any and all help in this matter is greatly appreciated,

Argus

There are several different ways of accomplishing this, but this should
work:

SQL = "Insert INTO tbl_web "INSERT INTO tbl_web_app " & _
"(ID, Pwd, Account) Values" & _
"('user_ID', 'user_Pwd', 'user_Account')"
MyConn.Execute SQL

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[OdAwG]

hey thanks for the reply, the solution was the following:

sql = "INSERT INTO tbl_web_app (ID, Pwd, Account) Values ('" & user_ID &"',
'" & user_Pwd & "', '" & user_Account & "')"
MyConn.Execute SQL

I had to add '" & variable_name &"' since I was using variable

Again, thanks for you help.

Argus
"Randy Harris" <pl****@send.no.spamwrote in message
news:gU******************@newssvr21.news.prodigy.c om...

* OdAwG:

Hello All,

Given the following code from several differenet sources within this
newsgroup, we were able to come up with a way to connect to a database

and

retrieve data from it. The code is listed below:

(******************************************)
01. Dim MyConn, rs, sql
02. Set MyConn = CreateObject("ADODB.Connection")
03.
04. MyConn.Provider = "Microsoft.Jet.OLEDB.4.0"
05. MyConn.Properties("Data Source") = "\\server\share\APPS_DB.mdb"
06. MyConn.Open
07.
08. Set rs=CreateObject("ADODB.recordset")
09. sql = "SELECT * FROM tbl_web_app"
10. rs.Open sql, MyConn
11.
12. Dim ID, Pwd, Account
13.
14. user_ID = (rs("ID"))
15. user_Pwd = (rs("Pwd"))
16. user_Account = (rs("Account"))
17.
18. some other work related code blah blah blah
19.
20. rs.Close
21. Set MyConn = Nothing
22. Set rs = Nothing

(*******************************************)

Now, what I am wondering is if there is a way using the code above to

insert

data into the tbl_web_app in the APPS_DB.mdb file? I tried the

following

but it doesn't work; nothing gets inserted into the database . I

replaced

the following:

line 09. with sql = "INSERT INTO tbl_web_app (ID, Pwd, Account) Values
('user_ID', 'user_Pwd', 'user_Account')"

I also removed lines11 - 17. I don't think I need them for the INSERT

part.

we have some other code before the database connection that sets the
variables for user_id, user_Pwd, and user_Account.

Any and all help in this matter is greatly appreciated,

Argus

There are several different ways of accomplishing this, but this should
work:

SQL = "Insert INTO tbl_web "INSERT INTO tbl_web_app " & _
"(ID, Pwd, Account) Values" & _
"('user_ID', 'user_Pwd', 'user_Account')"
MyConn.Execute SQL

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.