[EDIT]
The Digigraphy wrote:
My Database Location of First Table is- C:\Users\Digi\Source\Soft\BillData.accdb
And Second Database Location is
C:\Users\Digi\Source\Soft\Master\GroupList.accdb
Both these database are in different location. And I want to join them
You've got two ways to achieve that:
1) create one database and
a) link all tables from all databases
or
b) move (import) all tables from different databases into one
See:
Import or link to data in another Access database - Access[
^]
Then use this query:
SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1
INNER JOIN SecondTable t2 ON t1.Group_Id = t2.Group_Id
INNER JOIN ThirdTable t3 ON t1.Vendor_Id = t3.Vendor_Id
I'd reocmmend to use this way.
2) use
IN clause[
^]:
You'll need to modify the body of query this way:
SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount
FROM FirstTable t1
INNER JOIN SecondTable IN 'C:\Users\Digi\Source\Soft\Master\GroupList.accdb' t2 ON t1.Group_Id = t2.Group_Id
INNER JOIN ThirdTable IN 'C:\Users\Digi\Source\Soft\ThirdDatabase.accdb' t3 ON t1.Vendor_Id = t3.Vendor_Id
WHERE t1.Group_Id = ?
Above should also work, but it's more complicated.
So, changed code may looks like:
Dim firstFileName As String = "C:\Users\Digi\Source\Soft\BillData.accdb"
Dim secondFileName As String = "C:\Users\Digi\Source\Soft\Master\GroupList.accdb"
Dim thirdFileName As String = "C:\Users\Digi\Source\Soft\Master\ThirdDatabase.accdb"
Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};;Persist Security Info=False;", firstFileName)
Dim dt As DataTable = New DataTable()
Using connection AS OleDbConnection = New OleDbConnection(sConStr)
Dim sql As String = String.Format("SELECT t1.ID, t2.Group_Name, t1.Voucher, t1.Bill, t3.Vendor_Name, t3.Vendor_Address, t1.Amount{0}FROM FirstTable t1{0} INNER JOIN SecondTable IN '{1}' t2 ON t1.Group_Id = t2.Group_Id{0} INNER JOIN ThirdTable IN '{2}' t3 ON t1.Vendor_Id = t3.Vendor_Id{0}WHERE t1.Group_Id = ?", vbCrLf, secondFileName, thirdFileName)
connection.Open()
Using command As OleDbCommand= New OleDbCommand(sql, connection)
command.Parameters.Add(New OleDbParameter() With {.OleDbType = OleDbType.VarChar, .Value=2})
Dim reader As OleDbDataReader = command.ExecuteReader()
dt.Load(reader)
End Using
End Using
DataGridView1.DataSource = dt
Access connection strings - ConnectionStrings.com[
^]