add data to another database using ADODB

[dream]

Hi I'm new to access, My problem is i got a form with 2 textboxes and 1 command button on it and also a table with fields "password", "level" on the other database.

What i wanna know is whenever i insert data into the textboxes and click on the button it'll add a new record on the table in the other database. I need the code anybody please help...!

 

[dream]

here is the attachment for the above problem i wanna know when i type text in the textbox in db2 click on the button it'll transfer the text to the table in db1. Please help, urgent!

 

[Jon K]

Try this code in the On Click Event of the command button (replacing with the correct file path of db1.mdb):-

Dim SQL As String
SQL = "INSERT INTO Table1 ([Password],[Level])" & _
      " IN 'C:\My Documents\db1.mdb'" & _
      " SELECT '" & Me.Text1 & "','" & Me.Text3 & "'"

CurrentProject.Connection.Execute SQL

 

[dream]

wow its work.... But if i wanna delete the record or edit the record? how? And also i fi nv key in anything it will pop up an error where do i add a msgbox or command to let the person noe that the textbox must b key in..

I also add two labels, 1 & 2 if regsitering label1 is visible after everything have registered then label1 invisible and label2 visible

 

[dream]

Does anyone know about the error?

 

[WayneRyan]

dream,

In the OnClick event of the command button, where you insert
the two textboxes into the table in the other database, you
can first put:

If IsNull(Me.Textbox1) Or IsNull(Me.Textbox2) Then
   MsgBox("You must enter both textboxes.")
   Exit Sub
End If

To delete from a table in another database:

Dim SQL As String
SQL = "Delete From Table1 ([Password],[Level])" & _
      " IN 'C:\My Documents\db1.mdb'" & _
      " Where Field1 = '" & Me.Text1 & "' And " & _
                  "Field2 - '" & Me.Text2 & "'"
CurrentProject.Connection.Execute SQL

Wayne

 

[dream]

Hi wayne

What if i wanna edit the password or level?

 

[WayneRyan]

dream,

Just a guess, but if you had two more textboxes, Password andLevel
it'd probably look like this:

Dim SQL As String
SQL = "Delete From Table1 (" & Me.Password & "," & Me.Level & ")" & _
      " IN 'C:\My Documents\db1.mdb'" & _
      " Where Field1 = '" & Me.Text1 & "' And " & _
                  "Field2 - '" & Me.Text2 & "'"
CurrentProject.Connection.Execute SQL

Wayne

 

[dream]

so the command above is for editing?

 

[WayneRyan]

dream,

NO. The command above would delete a record from a table in
another database that matches the two textbox fields and
has username and password in the two new textboxes.

Wayne

 

[dream]

then how about if the user wanna edit the password how?

 

[WayneRyan]

dream,

As we built this up over the last few posts, we added the
username and password textboxes to your form. You enter
the username and password for the other database on your
form.

Wayne

 

[dream]

wayne,

i still dont get you

the code that i ask for is to add the id and password to the other database table and how do i get the data and edit from the other database?

 

[WayneRyan]

dream,

If that is what you want to do, why don't you just use a linked
table and base a form on it?

Wayne

 

[dream]

I also wanna do this but data in the database is all password that it only cannot be seen by other person so i've to do this.

I've got the entering and the registering and nw i just want to know the code for editing

 

[WayneRyan]

dream,

Dim SQL As String
SQL = "Update Table1 (" & Me.Password & "," & Me.Level & ")" & _
      " IN 'C:\My Documents\db1.mdb'" & _
      " Set SomeField = '" & Me.Text5 & "' " & _
      " Where Field1 = '" & Me.Text1 & "' And " & _
                  "Field2 - '" & Me.Text2 & "'"
CurrentProject.Connection.Execute SQL

Wayne

 

[dream]

so this command will auto edit the password...
And it'll reganise the user also right?

 

[WayneRyan]

dream,

That's the syntax, the names of the tables, the location of
the database, the names and values of the fields have to
be filled in by you.

Wayne

 

[dream]

wayne,

ok thanks alot.. I go and try get back to u later

 

[dream]

Wayne,


Dim SQL As String
SQL = "Update Table1 (" & Me.password & "," & Me.Level & ")" & _
" IN 'G:\Documents and Settings\IA_TRAINING01\My Documents\odbc.mdb'" & _
" Set SomeField = '" & Me.Text5 & "' " & _
" Where level = '" & Me.Text0 & "' And " & _
"password - '" & Me.Text2 & "'"
CurrentProject.Connection.Execute SQL



the line me.password gave an error
what does text5 do?