access uniquetable

C Coudou

Apologize if I post this on the wrong forum. In ACCESS, I have Form Open event that will connect to DB1 and datas will show on the detail records. The form has a Uniquetable = "tableEmp". Now, I have a option button that will change database to DB2, Note: DB1 and DB2 all tables are the same. In the form details I have checkbox, if I will checked the checkbox the tables update automatically, and it will update because of the uniquetable but If I will change the database the tables will not update. what's cause of this? thanks in advance,.

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Abhinav S

Check if the database db2 has the same table. If it does, the primary key that you update the table on may not be present in this table (in the second database).

C Coudou

thanks. i check the db2 and it has the same table. is my approach correct? i mean connecting other database in an access form. here is my snippet code: ACCESS FORM

'Default database DB1
Private Sub Form_Open(Cancel As Integer)
Dim ssql As String

ssql = "SELECT \[tbl1\].id, \[tbl1\].code,\[tbl1\].mflag, \[tbl2\].cdnum, \[tbl3\].bname"
ssql = ssql & " FROM  \[tbl1\] INNER JOIN"
ssql = ssql & " \[tbl2\] ON"
ssql = ssql & " \[tbl1\].id = \[tbl2\].id INNER JOIN"
ssql = ssql & " \[tbl3\] ON \[tbl1\].code = \[tbl3\].code"
ssql = ssql & " WHERE \[tbl1\].mdate ='2011/10/12'"
Me.RecordSource = ssql

Me.UniqueTable = "tbl2"

End Sub

'option to select DB1 or DB2
Private Sub optSel_AfterUpdate()
Dim ssql As String

'DB1
If Me.optSel = 1 Then
    ssql = "SELECT \[tbl1\].id, \[tbl1\].code,\[tbl1\].mflag, \[tbl2\].cdnum, \[tbl3\].bname"
    ssql = ssql & " FROM  \[tbl1\] INNER JOIN"
    ssql = ssql & " \[tbl2\] ON"
    ssql = ssql & " \[tbl1\].id = \[tbl2\].id INNER JOIN"
    ssql = ssql & " \[tbl3\] ON \[tbl1\].code = \[tbl3\].code"
    ssql = ssql & " WHERE \[tbl1\].mdate ='2011/10/12'"      
Else 'DB2
    ADODisconn 'disconnect to DB1
 
    ADOConDB2  'connect to DB2        
    
     ssql = "SELECT \[tbl2\].id,\[tbl2\].code,\[tbl2\].mflag,\[tbl2\].cdnum,\[tbl3\].bname"
     ssql = ssql & " FROM  \[tbl2\] INNER JOIN \[tbl3\] ON "
     ssql = ssql & " \[tbl2\].code = \[tbl3\].code"
     ssql = ssql & " WHERE \[tbl2\].id > 160" 
End If

Me.RecordSource = ssql
Me.UniqueTable = "tbl2"

End Sub

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Abhinav S

There is no UPDATE query in this code so its really difficult to figure out.

C Coudou

no. the update is on the form. Using the Continuous Forms The same as GridiView in visual basic. In detail of continuous form, I have textboxes and checkboxes, if I check the checkbox, the table is automatically updated. Database update is not on the code, its on the form.

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Lost User

C#Coudou wrote:

is my approach correct?
i mean connecting other database in an access form.

here is my snippet code: ACCESS FORM

It looks like VB.NET, but I don't see any OleDbCommand's. Are you using the "old" recordsets? FWIW; I don't see any code where you bind the data to the UI, nor where you re-open the new connection. The best suggestion I can give is to use the OleDb provider to access your database.

Bastard Programmer from Hell :suss:

C Coudou

nope, this is not vb.net, this is MS Access, that is why you cannot see an oledbcommand.yes, I'm using old recordsets. You can't see open connection because in the Form1 properties, it has recordsource, also in form open. If i will use oledb connection, how can i add the data's in my continuous form? This project is an .adp(microsoft access project),which means if you open the myproject.adp, it is already connected to database.

C# コードMicrosoft End User 2000-2008 「「「「「「「「「「「「「「「「「「「「「「「「「「「「 The best things in life are free 」」」」」」」」」」」」」」」」」」」」」」」」」」」」

Lost User

C#Coudou wrote:

nope, this is not vb.net, this is MS Access, that is why you cannot see an oledbcommand.yes, I'm using old recordsets.

My apologies, I assumed VB.NET - that's the name of the forum.

C#Coudou wrote:

If i will use oledb connection, how can i add the data's in my continuous form?

I don't think that will work; those reports are built for use within Access itself.

C#Coudou wrote:

This project is an .adp(microsoft access project),which means if you open the myproject.adp, it is already connected to database.

Not even sure whether Access allows switching the current connection. It's been some time ago that I worked in Access, and we didn't "switch" the connection to another database - we decided it was easier to import the data from other databases, and manipulate it in the current connection.

Bastard Programmer from Hell :suss: