Visual Basic

6-Feb-12 18:31

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 S6-Feb-12 19:12

Abhinav S

6-Feb-12 19:12

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#Coudou6-Feb-12 20:40

6-Feb-12 20:40

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

Abhinav S6-Feb-12 21:34

Abhinav S

6-Feb-12 21:34

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

C#Coudou6-Feb-12 22:07

6-Feb-12 22:07

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.

Eddy Vluggen7-Feb-12 7:41

Eddy Vluggen

7-Feb-12 7:41

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 Suspicious | :suss:

C#Coudou7-Feb-12 13:41

7-Feb-12 13:41

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.

update table programitaclly

Eddy Vluggen8-Feb-12 0:18

Eddy Vluggen

8-Feb-12 0:18

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 Suspicious | :suss:

Member 32752635-Feb-12 20:40

Member 3275263

5-Feb-12 20:40

hi,

i and updating my data gridview cell pragmatically.data is showing in the cell. but not updating but if i typed same value in that cell it is updating.

my code is

Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=SQG-133;User Id=" & My.Forms.LoginForm.UsernameTextBox.Text & ";Password=" & My.Forms.LoginForm.PasswordTextBox.Text & ";Initial Catalog=" & My.Forms.LoginForm.tb_database.Text & "")

'update statement----
conn = da_inv.SelectCommand.Connection

da_inv.UpdateCommand = New SqlCommand()

da_inv.UpdateCommand.Connection = conn

da_inv.UpdateCommand.CommandText = "UPDATE dbo.dt_finnance_value_adition SET bank_doc_ref_no=@bank_doc_ref_no WHERE fund_id = @fund_id"

With da_inv.UpdateCommand.Parameters
.Add("@fund_id", SqlDbType.Int, 0, "fund_id")
.Add("@bank_doc_ref_no", SqlDbType.NChar, 10, "bank_doc_ref_no")
End With

'update statement end
da_inv.Update(ds_inv, "inv_table")

Re: update table programitaclly

Mycroft Holmes5-Feb-12 22:14

Mycroft Holmes

5-Feb-12 22:14

The parameters you are adding contain string literals, you should be using the variables, trying to stuff "fund_id" into an .Int parameter is going to cause problems!

.Add("@fund_id", SqlDbType.Int, 0, "fund_id")
.Add("@bank_doc_ref_no", SqlDbType.NChar, 10, "bank_doc_ref_no")

Never underestimate the power of human stupidity
RAH

Graphic in DataGridView problem

Mangore755-Feb-12 14:10

5-Feb-12 14:10

I tried this code to display the data in dGV on the form, but it display the datat as a inclined,not as it show in the DGV , row after row.

For i As Integer = 0 To Me.DataGridView1.Rows.Count - 1
            For j = 0 To DataGridView1.ColumnCount - 1
                Dim g As Graphics
                g = Me.CreateGraphics()

                Dim str As String = Me.DataGridView1.Rows(i).Cells(j).Value
                           
                Dim y, X As Integer
                y += 20
                X += 30
                g.DrawString(str, New Font("Times New Roman", 12, FontStyle.Regular), Brushes.DarkGreen, X, y)
            Next
        Next

Richard MacCutchan5-Feb-12 23:29

Richard MacCutchan

5-Feb-12 23:29

Dim y, X As Integer
y += 20
X += 30

What are these values supposed to represent? I think you need to initialise them with something meaningful.

Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman

Mangore756-Feb-12 6:24

6-Feb-12 6:24

need to initialise them with something meaningful.
I didn't found any way about how to use them(X,Y)

Richard MacCutchan6-Feb-12 6:33

Richard MacCutchan

6-Feb-12 6:33

Mangore75 wrote:
I didn't found any way about how to use them(X,Y)

Then why are you using them, do you understand what they are for?

Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman

Mangore756-Feb-12 8:26

6-Feb-12 8:26

I used them y=y+1 and x=x+1 as I think that will print row after row and column after column after column, as I know by little information that I have.
by the way I'm not professional in using VB I'm still beginner.

Richard MacCutchan6-Feb-12 9:30

Richard MacCutchan

6-Feb-12 9:30

Yes but you have not initialised them to any starting point before you add the offsets.

Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman

Luc Pattyn6-Feb-12 0:23

Luc Pattyn

6-Feb-12 0:23

And then:

1. g = Me.CreateGraphics() is an expensive operation; why is it inside nested loops?
2. Graphics is one of those classes that offer a Dispose method; so if you create (or order creation) of an instance, you should dispose of it too.

Taken together, create once, dispose once.

D'Oh! | :doh:

Luc Pattyn [My Articles] Nil Volentibus Arduum

Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.

Mangore756-Feb-12 6:22

6-Feb-12 6:22

Why must Dispose it