By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,401 Members | 793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,401 IT Pros & Developers. It's quick & easy.

Insert into a database

P: n/a
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
Aug 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
* 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.
Aug 17 '06 #2

P: n/a
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.

Aug 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.