Linking Tables Ala Access Get External Data/Link Tables

Linking External Access Tables Via Code
Dear members,

I am trying to link 3 tables (system, Water_Source_DB and User) from a database located in C:Rehabilitated_Water_Supply_Kulyob.mdb" (Destination)to a database located in "C:WSS_Khatlon.mdb" (Source).

I need to perform this link via code outside Access using VB. I have successfully added the links using the link manager and then executed the code below, the tables are updated fine.

I need help with the code to set the links, then run the code below.

Any help will be most appreciated.

Thanks in advance.

Irshad



Private Sub CommandButton1_Click()

    Dim strTemp As String
    Dim Ptable As String
    Dim strSQL As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim strSrceDB As String
   


On Error GoTo ErrorHandler
    strSrceDB = "C:WSS_Khatlon.mdb"

'Make sure it is there
    If Dir(strSrceDB) = "" Then
    Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")
    
    Else
    strSQL = "UPDATE System INNER JOIN Water_System " & "" & ""
    strSQL = strSQL & "ON System.System_ID = Water_System.System_ID " & "" & ""
    strSQL = strSQL & "SET System.Longitude = Water_System.Longitude, " & "" & ""
    strSQL = strSQL & "System.Oblast_Name = Water_System.Oblast_Name," & "" & ""
    strSQL = strSQL & "System.District_Name = Water_System.District_Name," & "" & ""
    strSQL = strSQL & "System.Jamoat_Name = Water_System.Jamoat_Name," & "" & ""
    strSQL = strSQL & "System.Village_Name = Water_System.Village_Name, " & "" & ""
    strSQL = strSQL & "System.System_Type = Water_System.System_Type, " & "" & ""
    strSQL = strSQL & "System.Is_Working = Water_System.Is_Working, " & "" & ""
    strSQL = strSQL & "System.Dependance_Factor = Water_System.Dependance_Factor, " & "" & ""
    strSQL = strSQL & "System.Date_Not_Working = Water_System.Date_Not_Working, " & "" & ""
    strSQL = strSQL & "System.Storage_Capacity = Water_System.Storage_Capacity, " & "" & ""
    strSQL = strSQL & "System.Power_Consumption = Water_System.Power_Consumption " & "" & ";"
    
    
    strSQL1 = "UPDATE Water_Source_DB INNER JOIN Water_Source " & "" & ""
    strSQL1 = strSQL1 & "ON Water_Source_DB.System_ID = Water_Source.System_ID " & "" & ""
    strSQL1 = strSQL1 & "SET Water_Source_DB.Water_Src_ID = Water_Source.Water_Src_ID, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Village_Name = Water_Source.Village_Name, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Water_Src_Type = Water_Source.Water_Src_Type, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Borehole_Depth = Water_Source.Borehole_Depth, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Borehole_Diameter = Water_Source.Borehole_Diameter, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Drilling_Date = Water_Source.Drilling_Date, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Bottom_Screen_Depth = Water_Source.Bottom_Screen_Depth," & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Top_Screen_Depth = Water_Source.Top_Screen_Depth, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Static_Water_Level = Water_Source.Static_Water_Level, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Borehole_Casing = Water_Source.Borehole_Casing, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Latitude = Water_Source.Latitude, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Longitude = Water_Source.Longitude, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Elevation = Water_Source.Elevation, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Well_Tested = Water_Source.Well_Tested, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Yield = Water_Source.Yield, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Borehole_Rehabilitated = Water_Source.Borehole_Rehabilitated, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Borehole_Rehab_Date = Water_Source.Borehole_Rehab_Date, " & "" & ""
    strSQL1 = strSQL1 & "Water_Source_DB.Catchment_Date = Water_Source.Catchment_Date " & "" & ";"
    
    strSQL2 = "UPDATE User INNER JOIN Water_User " & "" & ""
    strSQL2 = strSQL2 & "ON User.System_ID=Water_User.System_ID " & "" & ""
    strSQL2 = strSQL2 & "SET User.System_ID = Water_User.System_ID, " & "" & ""
    strSQL2 = strSQL2 & "User.ID = Water_User.ID, " & "" & ""
    strSQL2 = strSQL2 & "User.User_Type = Water_User.User_Type, " & "" & ""
    strSQL2 = strSQL2 & "User.Village_Name = Water_User.Village_Name, " & "" & ""
    strSQL2 = strSQL2 & "User.Unit = Water_User.Unit, " & "" & ""
    strSQL2 = strSQL2 & "User.Unit_Quantity = Water_User.Unit_Quantity, " & "" & ""
    strSQL2 = strSQL2 & "User.Water_Meter = Water_User.Water_Meter, " & "" & ""
    strSQL2 = strSQL2 & "User.Consumption_Rate = Water_User.Consumption_Rate, " & "" & ""
    strSQL2 = strSQL2 & "User.Formal_Contract = Water_User.Formal_Contract, " & "" & ""
    strSQL2 = strSQL2 & "User.Latitude = Water_User.Latitude, " & "" & ""
    strSQL2 = strSQL2 & "User.Longitude = Water_User.Longitude, " & "" & ""
    strSQL2 = strSQL2 & "User.Elevation = Water_User.Elevation " & "" & ";"
    
    Set mDB = DBEngine.OpenDatabase(strSrceDB)
    Debug.Print (strSQL)
    Debug.Print (strSQL1)
    Debug.Print (strSQL2)
    Call mDB.Execute(strSQL)
    Call mDB.Execute(strSQL1)
    Call mDB.Execute(strSQL2)
    mDB.Close
        Set mDB = Nothing
        DoEvents
        MsgBox ("The Rehabilitated Water Infrastructure Database has been successfully updated!")
    End If
Exit Sub
ErrorHandler:
    strTemp = Err.Description & " [Update_SystemTab]"
    Call MsgBox(strTemp, vbCritical, "Contact Help Desk")
End Sub

---

URGENT: Linking External Tables Problem
Hi to all

I'm using this code to Link External Tables, but I get the Next Error.

Installable ISAM file not found (Translated from spanish)

Error number = -2147467259


Please I need help urengtly, because I need to deliver my program tomorrow.

Thanks in advance !!!



Code:
Private Sub CrerVinculo(DBDestino As String, DBOrigen As String, Pwd As String)

'On Error Resume Next 'Not Used Yet

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table


Set catDB = New ADOX.Catalog
' Open a Catalog on the database in which to create the link.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBDestino & ";Jet OLEDB:Database Password=" & Pwd

'catDB.Tables.Delete "JobsResp" 'Not used yet

Set tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property to the
' open Catalog to allow access to the Properties collection.
.Name = "JobsResp"
Set .ParentCatalog = catDB



' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = DBOrigen
.Properties("Jet OLEDB:Remote Table Name") = "Jobs"
.Properties("Jet OLEDB:Link Provider String") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBDestino & ";Jet OLEDB:Database Password=" & Pwd
.Properties("Jet OLEDB:Cache Link Name/Password") = True '<-save the password in the link

End With

' Append the table to the Tables collection.
catDB.Tables.Append tblLink

Set catDB = Nothing


End Sub

---

Linking Two Access Tables
We have connected to an Access database that consists of two tables. We have a one to many relationship between the tables. We are trying to display and navigate through all the records of both tables through an SQL statement. However, when we execute the SQL, only one table is able to be navigated through.

How do we set up the relationship so that the records of the table with foreign key are able to be accessed through the table with the primary key??

---

Linking Tables In Access
Hi all,

I'm trying to link a table in Access using VBA.
So far I've found the following code to do so (doesn't quite cut it)

Set tdfLocal = CurrentDb.TableDefs("myTable")
With tdfLocal
.Connect = ";Database="c:db2.mbd"
.RefreshLink
End With

This links the link myTable (a linked table in db1.mbd) to the (actual) table in db2.mdb.

The problem is: I need to link a table with a name different to the name of the link. This code doesn't allow this..

So, I need a link named "myLink" to link to a table called "myTable"

Can anybody help me, Frans

---

Linking MS Access Tables In ADO.
I'm converting an application from Access 97 (one database) and Access 97 tables linked to an Oracle server (another, different database). The previous developer wrote a Query in Access 97 (the one linked to Oracle) that linked, through SQL to the other Access Database to grab some information.

Is there a way I can replicate this in ADO?

Thanks,
Kyle

---

Linking Data From Different Tables
HI

I am using VB5 with MS Access2000 and I have two tables I am trying to link in the following way.
One table is Location and has two fields LocID and Location. LocID is an AutoNumber field. Location is a text field with the name of the Location such as Head Office. The LocID for Head Office is 10.
The other table is BaseData and has the field LocID that corresponds with the LocID in the Location table. I am running an SQL query on the BaseData table and one of the fields I extract is LocID. When I output the data to Text boxes on a form I want the Location text box to show the Location from the Location table that matches the LocID from the BaseData table. In other words, if the LocID number from the BaseData table is 10, I want the Location text box to show Head Office from the Location table.
I have looked at INNER JOIN and OUTER JOIN but am not able to get very far.

Thanks

---

DataBase With 4 Tables - How Do I Link Tables For Search
New to VB. I'm using VB 6.0
I am building controls to an ADO Data Control for a DataBase with 4 tables. How do I do a search that would pull information from more than one table at a time.

Example: One Database with...
Movie Actor Table
Movie Director Table
Movie Title/Actor Table
Movie Title Table


By selecting a certain Director, How can I pull up all movies directed by the certain director?

I have ID # that can be "compared" between tables, but I'm not sure how. Using a DataGrid to display the search results, how can I do a search that will search two tables???

Movie titles in the MOVIE TITLE TABLE include Director ID # which are also in the MOVIE DIRECTOR TABLE.

Any suggestions would be most appreciated.

MANY MANY THANKS,
Cameron

---

Linking Tables In Access Databases
Does anyone know if it is possible to link tables in Access db without showing the drive name ? (ex someone has XApps mapped to G drive and someone else to H)

---

Linking SQL Server Tables In Access
I would like some help over here, please.

I am trying to link SQL server tables in Access via VBA code, as an automated procedure. What I mean is that I want to create a linked Access table (linked with SQL server of course) out from zero. Can someone tell me if this thing is valid, or you just can't create a table from zero? Thanks.

---

Access DB And Linking Tables Via Code
I know how to create an access database and create tables via VB. But is there a way of creating a linked table meaning link a sql table to MSSQL table via VB Code?

Thanks!

---

Linking Access Tables Through Code
I can create an access CB with a bunch of tables and all kinds of fields. My problem is That I can't figure out how to link two tables based on a common field, specifically an id key.

For example, say I have table A and table B. Table A has a primary key. Now I want to have a field in table B that corresponds the the primary key in table A. I am using adox (I think that is what it is as I don't have my code readily available).



Troy Williams B.Eng.
fenris@hotmail.com
 
 

---

Linking To Microsoft Project Tables From Access
Using Access 2002 I need to link to the tables in Microsoft Project. I have the code to create a project object. Now just need some help connecting to the tables. Utlimiately I want to read data from Project. No updates will be done.

Thanks
Dan

---

Linking Tables From Sql Server Into Access With DAO Objects
Hey there, could someone help me?
I'm trying to link tables from SQL server into Access using the following VBA code (it's in DAO)

-------------------------------------------------------------------------
Dim db As Database
Dim tDef As TableDef

Set db = OpenDatabase(filename, False, False)
Set tDef = db.CreateTableDef("TEST")
tDef.Connect = "Provider=SQLOLEDB.1;Password=;UserID=;
InitialCatalog=databasename;Data Source=servername"
tDef.SourceTableName = "TEST"
db.TableDefs.Append tDef
-------------------------------------------------------------------------

when it's trying to append the table (in the last line) it gives me the following error:

Error: 3170
Could not find installable ISAM.

Does anyone knows what is this thing?

---

Want To Re-link Tables In Access 97
The following code is to re-link tables in Access 2000.
If I try to run the code in Access 97 it gives an error - variable not defined highlighting CurrentProject.
When I type CurrentProject and the period . then it will not come up with anything after it - I have ADO, ADOX, and DAO all checked off and referenced. Any idea what code I can use to replace the CurrentProject.Connection with?



Code:
Public Function RelinkTables(ByVal AccessFilePathName As String) As Boolean
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

On Error GoTo HandleErrors
RelinkTables = False
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set cat = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" Then
tbl.Properties( _
"Jet OLEDB:Link Datasource").Value = _
AccessFilePathName
End If
Next tbl
RelinkTables = True

ExitHere:
Set tbl = Nothing
Set cat = Nothing
Exit Function

HandleErrors:
MsgBox Err.Description, vbCritical, "RelinkTables"
Resume ExitHere
End Function

---

Changing Link Tables In Access Through VB
We all know that MSAccess has this feature called 'Link Table Manager' under 'Add-Ins' menu. Basically, this serves as the one that switches the database path of the linked tables from one
location to another. Now my question here is, is there a way you can manipulate the path of all your link tables in MSAccess through Visual Basic without using the 'Link Table Manager'?
Thank you and more power.

---

Link Tables To Access From Oracle?
I have a working connectionstring to oracle and I know how to create a tablelink from oracle. But I can only link one table at time.
How can I link all tables at the same time?
Now I do like this:

VB Code:
Dim db as databaseDim tdf as tabledefSet db = Currentdb Set tdf = db.CreateTableDef("LocalTable")tdf.Connect = MyODBCConnectionStringtdf.SourceTableName = "SomeTable"db.TableDefs.Append tdf

---

Can I Link A VB Form To An Access Db With 2 Tables?
I've successfully added an "ADODC" control to my form and linked to an Access db. Text boxes on the form are then populated from the various db data fields.

I have a combo box which I'd like to populate from a "child" table with the database but I'm only able to see the fields from the
"parent" table (there are 2 tables in the db with a one to many relationship i.e. group leader table to group member table).

Any help would much appreciated. Thanks in advance!

--
Richard
I'm taking the next bus outta here, I'm gonna head for Box Elder, M.O.

---

Access External Oracle Tables In Siebel
Dear Friends

Can some one please provide me the steps to access exteral Oracle Database Tables iin Siebel using VBC and Business Services

Thanks a lot in advance for your help

Regards

Balaramakrishnan

---

About Pivot Tables / External Data / Many Records
Hi guys and gals... well to make this short and to the point:

I have this Query that retrieves the production cost´s detail of Florida. When I update this to the month end (only one month each time) I have no problems. The querie retrieves an average of 33000 Rows, so I add to this about 2000 budget rows... and then I update a Pivot Table with all this...

But when I need to retrieve the data of two months (usually we work with actual and prior month analysis), well I get aboyt 65000 Rows plus the 4000 of budget... so obviously I cant put all this in one sheet... this is my goal: "Be able to put all the data into my Pivot Table" (I use 3 page fields... any of our analysis involves displaying all the data... so that is not a problem...)

So I was thinking in two scenarios:

1. Make two queries in two different sheets, add the budget and then make the pivot table with the join of these two sheets...

Troubles here: when I use the Pivot Table´s Wizard... I cant find out how to have my fields as if it was a single sheet... I only get {Page1, Page2, Page3, Rows, Columns, Values} fields... and I cant manage my original fields... so is useless (at least if there is no other way)

2. Make my Pivot Table directly from the Query (also from the Pivot Table Wizard) so this alouds me to put as many records as I want... but I dont know how to add the budget data to that pivot, because the budget data entry is always manual, so I cant use another Query...

I hope that someone knows how to deal with the PT...

TIA

---

Link Tables Of Access Database From Vb At Runtim
hi everybody,
can i have idea about linking tables of one database to another at runtime from vb.

Regards
Zenil

---

How To Link Data Report With Multiple Tables
I am creating data report. I have a database with 4 tables.
Table 1 : Tree
Fields : Treecode, TreeName, Height, Diameter, Cycle Life
Table 2 : SegmentPlan
Fields : TreeCode,SegmentCode, NumberOfTrees, DatePlanted, ForestOfficerInCharge
Table 3 :Segment
Fields: SegmentCode, SegmentPlan, State
Table 4 : Harvest
Fields :TreeCode, SegmentCode, DateOfHarvest, NumberOfTrees, ForestOfficerInCharger, Comments
 When I create the data report I only can link to one of the tables. I want to get all the fields from all the tables in one data report. How do I do that? I ve tried using data environment . I created the 1 parent command and 3 child commands but I can only link to 2 tables(Parent and 1 child)...Is there a way to have all my tables to create 1 data report.
 And how to acess data report according to "DATE" whereby user inputs the date.

---

Linking Access Tables To SQL Server 2000 With A Field Larger Than 255 Char.
I'm having a problem when I link tables in Access 97 (my client's version, not my choice!) to my SQL Server 2000 database. Several of the fields in some of the tables are either ntext(16) or varchar(3000) and when I link a table via ODBC it creates a text(255). To make matters worse, it doesn't even take the first 255 characters, if the data is longer than 255 char, it tosses out the first 255 and just takes the last few. Any ideas on how to convince Access to use Memo instead of Text?


Eamonn

---

How Do I Link A Form With 2 Different Tables In Microsoft Access Datbase
i need help on how to link a form(VB 6.0) with 2 different tables in access database and update the contents of the tables simulteneously from the form.
thanks

---

Connect To Database Visual Basic (Link Tables) Ms Access
Does anyone know how you can automatically connect to a back-end database (link tables) via visual basic. If there is no working connection I want to make the connection in visual basic The location of the back-end must be stored in visual basic.
What I mean with link tables is connect from a frontend database (Access) to a backend database (access) in visual basic. For the front end database it should look like the tables are in de front end hisself. So it must be able that the standard querys (access, not visual basic) work.

Greetings, Roel Frissen

---

Sybase Database Tables Combined With Custom Access Tables.
A problem i have is that i have to combine tables from a Sybase database and the ones i make in access, what i want is to be able to make proper Relations between the ODBC and local tables. But i don't know how i can do this, anyone with tips and or urls with explainations would be more than welcome.

---

Tables Catalog Includes Access System Tables
Hi,
I'm using ADO with the code below to load table names from an Access database into a Listbox. When I get the names using ADOX.Catalog, inaddition to the User tables, I'm getting all the hidden system tables, MSysACEs, MSysModules, etc. How can I gather only User Tables?

Thanks in advance.
tsallGood

Code:
    rsData.ActiveConnection = strConnect
    'Get tables catalog in active connection and load list of tables in Listbox
    Set adCat = New ADOX.Catalog
    adCat.ActiveConnection = rsData.ActiveConnection
    For intTabCount = 0 To adCat.Tables.Count - 1
        lstTables.AddItem adCat.Tables(intTabCount).Name
    Next


--If life serves you lemons...
Ask for salt and a bottle of tequila

---

Access 2000 Linking To External Data
Hi,

I have a small problem when I link to external data (paradox *.db) in Access 2000. What makes my problem really annoying is the fact that it only does it on certain computers.

Basically I am developing a VB form which is the front end for a Access 2000 database. The database contains its own tables and a couple of links to external database (Paradox).

The problem I am having is that on certain computers, the readwrite speed is alot slower than that of other computers. The computers themselves are all the same spec, so I quite ceratin it is a driver issue. Does anyone know how to find out what drivers Microsoft Access 2000 is using to connect to the external database source (version, etc)

Thanks

Slim

---

Linking Access Data To An External Word Document
I have a Access Data Base that tracks Patients each Patient has a word Document created for them that is a discharge summary. In my table I have a concatenation field that generates the file path to this document. My problem is I need to have a command button that opens word and the Document via this file path. I thing VB gives me the best option of this however I have not been able to get the Field (file Path called [Cas] to work. I have gotten word to open however the file path still reads as "[cas] not the product of the catenation. So anyone able to help ?

---

Reading Access Tables Then Writing To SQL Tables
Hi! I want my program to run an infinite loop that reads new data every 5 mins from an Access 97 table, then append it to an SQL Server 2005. I'm still using VB6.

This loop should start automatically in the background when the VB program is started.

I was thinking of putting the code on the Form Load part of the program. Any thoughts on how to code this?

---

Access System Tables - Need Columns As Well As Tables
I have one function that works great that compares SQL tables. I am trying to turn that into a second function that will compare Access mdb tables and columns within the tables.

This works for SQL

Code: cmSQL.CommandText = "SELECT * " _
        & "FROM INFORMATION_SCHEMA.COLUMNS i " _
        & "JOIN SysObjects s ON s.Name = i.Table_Name " _
        & "Where s.Type in ('U','V') "
    Set rs = cmSQL.Execute

However, there doesn't seem to be a comparable table, besides MSysObjects, in Access (which appears to only hold objects). Is there a spot that holds the columns in all of the tables?

Am I missing something or can I only compare tables and not columns and tables?

~Elizabeth~

"A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila "

---

Updating SQL Tables With Data From ORACLE Tables
I am tyring to write a stored procedure which will refresh tables on SQL Server with Data from corresponding Tables in ORACLE. The Oracle database is linked via a Linked Server in SQL. I have witten the following SP:

CREATE PROCEDURE dbo.sp_RefreshDDMSTables

AS

DECLARE @strSQLTable varchar(100)
DECLARE @strDDMSTable varchar(100)
DECLARE @strSQL varchar (100)

DECLARE rs CURSOR FOR
SELECT [Name] FROM dbo.sysobjects WHERE left([name],7)='T44DDMS' AND xType='U'


OPEN rs
FETCH NEXT FROM rs INTO @strSQLTable

WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = 'TRUNCATE TABLE ' + @strSQLTable
SET @strDDMSTable = substring(@strSQLTable,9,len(@strSQLTable)-8)
EXEC (@strSQL)
SET @strSQL = 'Insert Into ' + @strSQLTable + ' SELECT ' + @strSQLTable + '.* From P48..T44DDMS.' + @strDDMSTable
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
EXEC (@strSQL)
FETCH NEXT FROM rs INTO @strSQLTable
END

CLOSE rs
DEALLOCATE rs
GO

Whe this SP is executed the following message is returned:

Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Can anyone help please?

Thanks

---

Tables And Procedures Of Access Tables
i somehow was able to get the table names of an access xp database thru ado in vb6. the problem is that the returned fields contains stored procedures and queries too. how do i know which is which?

and thanx in advance

---

Access: Getting Data From Tables
I need to get two pieces of data from a table (Forename and Surname)
and display them in a combo box with a space between the forename
and surname. How would I go about doing this? I'm using access.

I'm ok with normal VB, but VBA confuses me. Can anyone tell me where I
can find some good tutorials aswell?

Thanks

---

VB6 And Access Tables/Data
I am setting up new forms in VB6 which using the VB Data Form Wizard allow me to connect to my tables in MS Access. I am now at a stage where some forms display the first record of the Access Table and I am able to page down/up through the records. However, although I can change the data there is no way to save the changes. Any thoughts ? Also with a few tables I get the following error when I open it's form... "Data error event hit err... No value given for one or more required parameters". When this happens no records or data is shown allthough form and all the tables fields are visible. I presume there is something in the access table that is causing this but not sure what.
Once again, any help on either of these two problems is much appreciated.

---

Linking Many-Many-Many Tables
I posted this in another forum but I think I was in the wrong spot and this is the right one...If not then I apologize.

I desperatly need some help with this. I'm tyring to figure out the best tactic for approaching the problem ( I know there is more than 1) below.

I have 3 VERY similiar tables, each has an account number listed many times, and different ServiceIDs. The table rows look like this

11SameAccountNo ServiceID1
11SameAccountNo ServiceID22
11SameAccountNo ServiceID14
11SameAccountNo ServiceID38
19DifferentAccount ServiceID99
19DifferentAccount ServiceID4

The same account exists in all 3 tables (we'll call them table1, 2 and 3), and exactly as above in each, but they MIGHT or MIGHT NOT have the same services....

I need to find accounts with identical services attatched on all 3 tables, and how many matches/mismatches. Ideally, I'm hoping to go beyond just match vs. mismatch and report on which ServiceIDs are matching the most and the least.

I thought about loading them into recordsets, then have a script loop through the first table and nested within that loop, go through the other 2 tables, looking for matches, but his seems very slow and inefficient.

Anyone have any good ideas for a quicker more efficient way, or am I already on the right track?


Using Access2003 with XP sp1

---

Linking Tables
Hi

I know there is a fair bit on linking tables and have looked at some samples I have some code which I think looks reasonable to comprehend however having tried a few ways to link the SQLserver database I keep getting the error

ODBC -----connection failed to SQLSRVR1
SQLSRVR1 is the name I have tried without the <SQSRVR1> and put it like "SQLSRVR1" and 'SQLSRVR1'

I should have no problems connecting as I have full permissions.. Could anyone give me a point in the right direction please

Thanks for any help

Code:
Set cat = New ADOX.Catalog
Set tbl = New ADOX.table
Set cnAccess = New ADODB.Connection
Set rs = New ADODB.Recordset

cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.path & "Test.mdb;"
cat.ActiveConnection = cnAccess
tbl.Name = "Tels"
Set tbl.ParentCatalog = cat

tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver=SQL Server;Server=<SQLSRVR1>;database=TelephoneInventory;uid=<username>;pwd=<strong password>;"
tbl.Properties("Jet OLEDB:Remote Table Name") = "myTels"
tbl.Properties("Jet OLEDB:Create Link") = True

cat.Tables.Append tbl

---

Linking Tables
hi,

Any views on how to link a table from one database to another using vb code.
Both databases are access.

Thanks in advance

Ian.

---

Help With Linking Tables
I am trying to come up with a way to duplicate an old DAO technique using ADO. I want to get a large amount of data (through a VB App) from a SQL Server or Oracle DB into an MS Access DB. In the past, I used DAO and performed a query like
"Select into x in "c:Accessdb.mdb"". This would create the destination table in the AccessDB and quickly copy the data. I'm trying to perform a function similar to this using ADO, but I'm not sure how to do it. I though of programmatically linking my source tables in my AccessDB, but I can't see how to do that as well.

---

Linking Tables
How can i link 2 tables across 2 access mdb files in single query ?


Thanks.

---

Linking Tables
I thought I knew, but I'm stoooopid. How do you link tables in an access database in VB?

---

Linking 2 Tables With 2 ADO&#180;s With Same ID
Hi All!
Need some advice on the folowing problem:
What I have is:
(VB6)
1 x Ado connected to table A
1 x Ado connected to table B
Both tables (A+B) have the same column (ID)

What do I need to do that table B moves when table A moves on next record using the same ID column. So I need to connect the 2 ID-columns???

Many Thanks,
Praenobilis

---

Linking Tables
How can I link ACCESS Database tables in VB5 using DAO.

---

Grabbing Data From Other Tables In Access
I am making some forms in access, they use the VB code builder to make some calculations on text fields. I want to know how i can use an SQL statement or something like that from within the code builder to grab values from a different table, so that I can make calculations with them.

Cheers

- MunX the VB newbie

---

Meta Data For Access Tables
How do I retrieve meta data for tables in access?
Basically I want to find out if a column exists in a table...

Anyone?

Woka

---

Data Control And Access Tables
In my program I wrote the code so a CommonDialog box appears so the user can select an Access Database. Now, I want another dialog box to prompt the user so they can select the name of a table in the database file; so I can set the RecordSource property of the Data Control the database is bound to. I dont know how I can get this done.

---

How Do I Access Data From Two Separate Ms Access Tables To Display To A Single Form
I'm working with a database which has 3 tables. the first one has the employee info, the second, the deduction and allowances and the third the payperiod info. now what i have to do is display [lastname], [firstname] & [mInitial] - (all found in the employee info table) - and the due payment for loans and total allowances availed (from the deduction and allowances table) to a visual basic form. i made use of this (im not really sure if it's the right code, anyway it doesn't work... everytime i run the project, the form shows up but it displays nothing except the employee information (lastname, FName and MInitial):

...adoPrimaryRS_O.Open "select ( [LName], & chr (32) [FName], & chr(32) [MInitial] as Name from tblEmployee, " & " [Total Deduction], [TotalAllowance] from tblDed_Allow," ...

can anyone help me with this? tnx...

---

Linking Outlook Tables
Hi everyone,

You can linked outlook contacttables in your MsAccess application. I am searching for some code in which I can do this automatically when I started up my application.

I wanna searches in Outlook wheen contactfolders are available and every table has to be automatically linked in the Access application.

Has anyone have an idea?

---

Linking 2 Tables From 2 Different Database?
Hi, I have a problem here, I have two access database, 1 uses DAO (access 97) and the other uses ADO (access 2000), is there anyway which I can link the two tables from these two database? I need the information from one to the other.

---

Primary Key/Linking Tables
Hi everyone...
I am running SQL Server 2000
I have 2 tables, and in table A, I am using a autonumber for a primary key. When I add a record to table A, I also need to add a corresponding record to table B with the same primary key.
My problem is when I add a record to table A, I can't get the primary key unless I run a query right after the update to get the primary key. Is there something that can be added to a query to acheive this or is there something that I need to setup in SQL Enterprise Manager to do this?

Any help is appreciated

JDavis

---