Modify Linked Table?


I suspect this may not even be possible, but it's worth asking ...

Is there a way to programatically add fields to a Linked table in a MS Access Back End MDB?

I'm using MS Access 2000. Front End MDB contains my code, Back End contains data. Opening the Front End allows selection of the case to be worked on, then Links a set of tables from Back End to Front End.

Catch is that Back End tables are defined by a third party over which I have no control and they often add fields to their table definitions. When that happens, I have to add the same fields to the appropriate tables in my existing Back Ends.

Right now it looks like I need to write a Validation routine in the Front End to test the tables in the back end for possible missing fields, then instruct the operator to run a Fixer app from within the Back End to add the fields.

I'm looking for a better way.

Thanks in advance for any thoughts/suggestions.


Sponsored Links:



Hi,

I am currently working on a modification of MS Access frontend application using VBA coding.

Given: I have a list of link tables connecting Oracle and MS access with info stored in these tables
I have to filter the data by expense date field and submitted date field, another world, I have to
create four text boxes on the form : txtExpenseStartDate, txtExpenseEndDate, txtSubmittedStartDate, txtSubmittedEndDate, so when the user can pick the start date and end date and clicks on Export button the right amount of data exports in Excel.

Requirements:
I have to modify the strSQL according input values of start and end dates.

Please guide me as to how to go about this problem.

I would be thankful if you could also give me a sample code to understand

Thanks in advance for the help ,



Function ExportData_LavishEntertainment(xlApp As Excel.Application, ws As Excel.Worksheet) As Boolean
'Returns true if successful, false otherwise.
Dim strSQL As String
Dim strMsgBoxTitle As String
On Error GoTo ExportData_LavishEntertainment_Err

strMsgBoxTitle = "Exporting Lavish Entertainment"

'If the report format changes this will need to change.
'Spreadsheet columns are defined, so use explicit select statement below (don't use "select * from").

strSQL = "SELECT tbleQuest_LavishEntertainment.User_Name2, tbleQuest_LavishEntertainment.User_Corp_Id2, " & _
"tbleQuest_LavishEntertainment.Expense_Type_Ke y, tbleQuest_LavishEntertainment.Er_Number, " & _
"tbleQuest_LavishEntertainment.Er_Title, tbleQuest_LavishEntertainment.Er_Line_Number, " & _
"tbleQuest_LavishEntertainment.Expense_Date_Ke y, tbleQuest_LavishEntertainment.Submitted_Date_Key, " & _
"tbleQuest_LavishEntertainment.Expense_Description , tbleQuest_LavishEntertainment.Purpose, " & _
"tbleQuest_LavishEntertainment.Expense_Amount, tbleQuest_LavishEntertainment.[Num of Attendees], " & _
"tbleQuest_LavishEntertainment.[Per Person Formula], tbleQuest_LavishEntertainment.A, " & _
"tbleQuest_LavishEntertainment.B, tbleQuest_LavishEntertainment.C, tbleQuest_LavishEntertainment.Disp_Comments, " & _
"tbleQuest_LavishEntertainment.Status " & _
"FROM tbleQuest_LavishEntertainment " & _
"ORDER BY tbleQuest_LavishEntertainment.Er_Number, tbleQuest_LavishEntertainment.Er_Line_Number;"



'White background.
ws.Cells.Select
With xlApp.Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With

'Populate the data.
If Not ExportData(strSQL, 6, xlApp, ws, strMsgBoxTitle) Then
ExportData_LavishEntertainment = False
Exit Function
End If

'Formatting specific to Lavish Entertainment output.
ws.Columns("G:H").Select
xlApp.Selection.NumberFormat = "mm/dd/yyyy"

ExportData_LavishEntertainment = True

ExportData_LavishEntertainment_Exit:
Exit Function

ExportData_LavishEntertainment_Err:
ExportData_LavishEntertainment = False
MsgBox "There was an issue exporting lavish entertainment data.", vbCritical, strAppTitle
Resume ExportData_LavishEntertainment_Exit

End Function









What would be the most effiecient way to run a MakeTable with VBA objects for all linked tables? Total network transfer will be about 60 MB.
A record count verification or some type of verification would be useful too.

While I realize this could be done with SQL scripts on a schedule, this requirement is a data pull when some conditions are satisified. Assume that SQL can't be modified for this exercise.
My thoughs are to loop through objects and just do a MakeTable query. But, there is always another consideration out there.

My plans are to start on the code now and see if anyone out there has other suggestions.




I need to display a Price per Square feet calculation on a report. The problem is that, the query that calculates this is based off a linked table that can sometimes contain a zero value in the square feet field. The data can be manipulated if need be (although I didn't think it was possible to modify a linked table) but it needs to be automatic so the user doesn't know what is happening. I figured that i would need some type of do until loop in vb to accomplish this, but not sure how exactly to go about it. Any ideas would be greatly appreciated. Thanks in advance.




Howdy, I'm self taught here so if I'm asking a dumb question I apologize. I've created an Access FE with a MSSQL BE. I don't want to have to create an ODBC connection for all the users (lazy I guess) so I found some code on the web that will allow me to not have to have odbc linked tables, modified it a bit and it seems to work fine. I created a table containing all the names of the tables I have in MSSQL, loop through those and create/refresh the tables. Please advise if I should pursue other measures.

Thanks

Public Sub CreateLinkedTable()

'Used to hold tabledef object and connection string
Dim td As TableDef
Dim ConnString As String
Dim LocalTableName As String
Dim SourceTableName As String
Dim SourceServerName As String
Dim SourceDatabaseName As String
Dim Login As String
Dim SourcePassword As String
Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb

strSQL = "SELECT [TableName] FROM tblnetworktables;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

Do Until rs.EOF

'Sets all variable values for connection string
LocalTableName = rs(0).value
SourceTableName = rs(0).value
SourceServerName = "xxxxxxxxx"
SourceDatabaseName = "xxx"
Login = "xxxxxxxxx"
SourcePassword = "xxxxxxxxxxx"

'Creates connection string for the linked table
ConnString = "ODBC;DRIVER=SQL Server;SERVER=" & SourceServerName & ";DATABASE=" & SourceDatabaseName & ";UID=" & Login & ";PWD=" & SourcePassword

'Sets the table definition object
Set td = CurrentDb.CreateTableDef(LocalTableName, dbAttachSavePWD, SourceTableName, ConnString)

'Creates/refreshes the table by appending to the current list of table definitions.
On Error Resume Next
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub




Hello,

How do I append a relation between, two linked tables, to a database in VBA?
I can do this fine manually (using the relations table) but I get the following error at the line

"CurrentDB.Relations.Append rel"

"Run-time error 3057: Operation not supported on linked tables"

I have two tables that are linked to excel spreadsheets. The location of these spreadsheets may change and rather that have the user go in and manually change the connection, I would rather have them change it using a form.

In the code below I get the spreadsheet location from a textbox, create a new table (as the connect property of the original is read only), delete the original relation and table and append the new table. The relation append will not work though. I'm using Access '07.

Code:


	Code:
	Private Sub btnLinkXls1_Click()
    Dim tblSpreadsheet1 As Object
    Dim connectString As String
    Dim sourceTblName As String
    Dim tdfNew As TableDef
    Dim rel As relation
    Dim fld As Field
      
    'Note - The connection string is in the following format:
    '"Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=connectString;TABLE=Sheet1$"
    
    'Get the value entered into the text box
    txtXls1Location.SetFocus
    connectString = txtXls1Location.Text
    
    'Build the connection string
    connectString = "Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" + connectString + ";TABLE=Sheet1$"
    
    'Create new table as property is read-only and cannot be modified
    
    'Get original SourceTableName
    sourceTblName = CurrentDb.TableDefs("Spreadsheet1").SourceTableName
    
    'Copy existing table and relation
    Set tdfNew = CurrentDb.CreateTableDef("Spreadsheet1")
    Set rel = CurrentDb.CreateRelation("Spreadsheet1Spreadsheet2", "Spreadsheet1", "Spreadsheet2")
    Set fld = rel.CreateField("orderID")
    fld.ForeignName = "order number"
    rel.Fields.Append fld
    
    'Change the connect property and source worksheet in the copy
    tdfNew.Connect = connectString
    tdfNew.SourceTableName = sourceTblName

    'Delete original table and relation, relation first
    CurrentDb.Relations.Delete "Spreadsheet1Spreadsheet2"
    DoCmd.DeleteObject acTable, "Spreadsheet1"

    'Create the new table
    CurrentDb.TableDefs.Append tdfNew
    
    'Add table relations
    CurrentDb.Relations.Append rel '******ERROR OCCURS HERE*****
    
    'Refresh!
    CurrentDb.TableDefs.Refresh
    
End Sub

Thanks for any help.




Hello

I have an access 2007 database and have migrated all the tables over to SQL 2005 Server Management Studio in order to add security to my database. I used the access wizard for migration via triggers.

One of my main forms is used to add new users or it can also be used to modify any existing user details by first selecting from a combo dropdown in a seperate form. In this main form there is a tabbed control area and each tab contains a linked subform. This all worked fine until I migrated my tables to SQL.

If a user is modified everything still works ok and data can be added or amended in the main form or linked subforms. However when trying to add a new form, data can be added to the main form and first sub form but the moment I click off of the sub form or even try accessing other sub forms (either before or after adding data to the first sub form) I recieve the following error
ODBC--insert on a linked table 'T_User' failed.

I have checked and recreated my relationships in SQL, my database is in a trusted location, all tables have a primary key and I also have foreign keys connecting tables. I have checked in my forms and sub forms that the master and child links are correct and also that the sub forms include the relevant foreign key and finally all data properties are set to Yes for adding or editing records.

I would be grateful for any input or light anyone could shed on this.

Many thanks

Kelly




I have access 2007 which I have created a DNS to SQL server hosted somewhere on the internet. I need to create a query in access to modify the data on the server side through linked tables, and send this access file to my client to make the changes. Therefore the linked tables have to remain "linked" when the client uses the query.

I already created the ODBC and named it a specific DNS, and I can see the data fine. I want to somehow keep this link intact when I give the file to the client so that they don't have to create a ODBC connection.

Is there a way to employ this connection embedded into the file? Do I have to use Visual Basic Code somehow?

Any help would be greatly appreciated.




Firstly, hello to everyone here at the access-programmers Forum, I hope you're all very well!

Secondly, I'm after some, well, help really.

I've been lumbered with what I believe is a front-end/client linked style database (I suppose that's what Access is really) with which I've been struggling with what is most probably an extremely easy problem for around a week now; I don't like being beat, but this has me beaten.

First off, please excuse me for not knowing the exact access terminiology, but I'll do my best.

So let's begin.

Problem: I want a checkbox on the Form "Order Acknowledgement" Inside of Client_2003.mdb.

So I create a checkbox called "proforma pay" and I get a little green triangle in the corner of the check box - I assume that's because it's not linked to anything. Ok I get that.
In the normal view, the error is "Control can't be edited; it's bound to unknown field 'Proforma Pay'.

So this is where I'm getting stuck.

I've tried to create a new column in the linked table, but it can't be modified because it's linked. Ok I get that.

So, I go to the back-end and I go to design view, and I get this error message: "You can't open the table 'Order Acknowledgement' for modification."
A query or form bound to the table is open and you may not have permission to open this table in Design View.. bla bla bla.

So does that mean I have to create a new table in the back-end and then link it?

All I want is for a checkbox to be able to be checked if a Proforma Invoice has been paid, but at this present time, the box is just greyed out - assuming because it's not linked to anything.

Thanks in advance !




hi folks,

i've recently placed a split dbase on a server with the back end on a shared drive and the front end on individual workstations. i've read setting up a multi-user environment this way makes the dbase run faster (jet runs on each workstation) and reduces the network traffic. here's the problem. the network administrator just told me that i need to move the back end to another directory on the server. my question is how do i access the linked tables (front end) reference path to the back end. do i have to delete all of the tables on the front end applications, move the back end, and then re-link the tables or is there an easier way to do this? in general, how do you access the reference path of a linked table if you need to modify it? thanks to everyone for any help with this.




Hi all,

I am having a problem with this linked table(and the only table) in access which is linked to a database in MySQL via ODBC. the problem is that the linked table in access is read-only (I want it to be read and write).

I have searched the internet and found that you need to have the primary key defined, but I have already done that.

This is what I have done: I created the table in access and then exported the table to MySQL. I have then modified the table in MySQL as shown in attachment. I had to redefine the primary key, because Access did not. I then made a link back to access which now works except for it being a read only table.

Operating system: windows 2000
Office version: office 2000
MySQL version: mysql-4.1.7 for windows
ODBC driver version: MyODBC-3.51

I do not know What I have missed, and thought you smart people may be able to help me out.

Thanks very much for any help given.




There has to be an easy answer to my problem, but I give up. I'm pretty much a novice trying to modify what seems to be a fairly complicated database. Here goes.

The format for all of the database tables are stored in a master database and linked to the database (user database) with all of the forms, queries, etc. I am trying to add fields to one of the tables. I added the fields in the master database, made a new linked table in the user database, and created the links in the master database. My problem seems to occur with the relationships for the user database. When I create the relationships, the check boxes to enforce referential integrity, etc are shaded and cannot be checked. I am working from a copy of the database so that I can compare to the original that works. In the original database, all three boxes (enforce referential integrity, etc) are check in the master database and in the user database only enforce referential integrity is checked. In the user database, the relationship type is "One to Many - External." It SEEMS that my problem is that I can't check the "Enforce Referential Integrity" box for my relationship. Also, I don't know how to get "One to Many-External" to appear. Mine just shows "One to Many."

The error caused by my problem is as follows:

I have a subform where a combo box is used to populate a field. The source items for the combo box appear properly, but when I try to select an item input after I modified the linked table, I get the following error "You cannot add or change a record because a related record is required in Table xx." Everything I can find indicates this is a relationship problem. Interestingly enough, if I select an item that I input prior to change the table, it is accepted and input without error.

I know this is long, but I don't know how else to explain the problem.
Thanks




This question will surely show my amateur Access skills... I have to add a field to a linked table. However, the table doesn't save my changes. Is there something I need to do before I make my change?

I get the error message "Table 'name' is a linked table with some properties that can't be modified. Do you want to open it anyway?

Any ideas? Thank you!




Hi all,

I have an Access DB with hundres of queries and reports, now I have to use an external DB (Oracle) and export data keeping the rest, no problem with that, I have added linked tables through odbc and works fine.

My problem is that some of the tables have field names with spaces, and Oracle doesn't admit them. Does anybody knows how to solve it without having to modify all the queries, etc?, I've been thinking about aliases or views over the linked table but I haven't found a way to create these.

Any help?

Thanks




Can someone tell me how to go about adding security to a field in a linked table so that only certain people can access and modify the information? I am using Access 2002. I also wonder if someone can tell me how to modify a field in a linked table? I have tried altering and saving the changes, but it won't save them because it's a linked table. Thank you in advance to anybody who can help me out here!




I have a nice application built with several tables related to each other. This is an application with a split database which resides on a server and an installable application that I put on individual workstations.

I encounter a problem when I want to update a table format, say add a new field or a validation rule. I get a message that I cannot modify a linked table. Do I have a "linked" table? It seems incorrect that I could not add a field (not, say remove a related field) or change a validation rule.

Can someone help me figure out what I am missing here. Any help is appreciated.




Deeper and Deeper in the rabbit hole I go -

I have a DB that is linked to three Excel sheets. The data is easily updated on a weekly basis from the sheets in Access. (no prob there)

The issue I have is creating a Primary Key in the 3 tables to create proper relationships (One-to-Many)
I'm unable to modify tables that are linked. (or can I ?)

I've created a column in each spreadsheet for a unique number, but cannot define the Primary Key in Access. Access is saying "...can't save property changes for linked tables" (yes...I'm trying to create a decent query using both tables)

Is there a work-around for this?

BTW - this website is great - helped me out many a times




Hi, this is my first post...so please be gentle!

I currently have all of my tables in one .mdb file that is saved in a folder with NTFS permissions attached to it...
Elsewhere on the drive i have the 'front -end' which is also a MS Access file, all of the tables are linked to it...

I was wondering if there was any way possible to enable users to modify the data within the tables via access if they do not have a required NTFS permissions where the linked tables are located?

Hope you can help!

Brendan




I split my Access 2007 DB to FE and BE.
The FE has been distributed to multiple users.

The BE is on my system and I have granted full control access (read and write) to all the folders and subfolders where the BE Access 2007 is located.

I have been informed that all users get the information that the BE is read-only and they are unable to modify any records on BE database.

They can only view the existing records.

However, As I had created the BE, I can modify the records at the BE database.

Also when the users try connecting to the BE directly frfom Access 2007 UI, they get the prompt that the BE is read-only and need to Link the tables.

Manual Linking the tables also doesn't work as the NEW linked tables also shopw as read-only.

Please let me know what needs to be done from a networking point of view.




Hi I want to link two tables together, both tables has a field called opr_nbr
This is the operation number (05 for example)
The trouble is in one of the tables the data is a number in the other is text.
The fields are outputs from legacy systems, I want an easy way to link these tables so that I can perform queries on them.

the problem I find is im getting a data mismatch error, as the data is showing as not the same.

When looking at the tables one shows 05 and the other would show 5

is there a way of converting the field during a query. I am unable to modify the table itself, only query from it.

I have been scratching my head on this one for a few days, done some searches on here and still come up with nothing. Hopefully one of you guys can help.

Thanks