Modules & VBA :: Linking External Tables To Current Database?
Mar 16, 2015
I was wondering if it was possible to link random external tables to the Active Database through VBA. I would like to run the code that would open up a dialog box that would let the user select the database as well as the tables within that database that the user can select to link to. I am able to select the database and but not able to select the actual tables. The tables will be random so I can't make a constant statement for a specific database.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database Option Explicit Private Sub AttachPaymentDetails() Call PerformInsert("tblFinancialBudget", "frmFinancialBudget") End Sub
I'm currently using the following VBA to export all tables within my DB to Excel on separate tabs:
Dim td As DAO.TableDef, db As DAO.Database Dim out_file As String out_file = CurrentProject.Path & "" & "Backup.xls" Set db = CurrentDb() For Each td In db.TableDefs If Left(td.Name, 4) = "MSys" Then 'We do not need MSys tables in excel file Else DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ td.Name, out_file, True, Replace(td.Name, "dbo_", "") 'We do not need dbo prefix in sheetnames End If Next
But upon exporting I get the following error:
"Run-time error 3274' External table is not in expected format"
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
I would also like to automatically attach on the server to this email. Such a file stored such as servernamesharefilename.pdf. Is it possible to do this?
Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog
Dim tblLink As ADOX.Table Set tblLink = New ADOX.Table
' Open the catalog. cat.ActiveConnection = cnn
With tblLink ' Name the new Table and set its ParentCatalog property to the ' open Catalog to allow access to the Properties collection. .Name = "PROJMSTR" Set .ParentCatalog = cat ' Set the properties to create the link. .Properties("Jet OLEDB:Create Link") = True .Properties("Jet OLEDB:Link Datasource") = strProvider & "F:PROJMASTER.mdb" .Properties("Jet OLEDB:Remote Table Name") = "PROJMASTER" End With
' Append the table to the Tables collection. cat.Tables.Append tblLink
Set cat = Nothing
End Sub
what am i missing??
oh, and i shouldn't see the table in the db container either, correct?
I have a Back end (with password) which resides in a netdrive while the front end is installed in each individual users desktop, the problem is, some of the users netdrive was mapped in a different way (different letters..some are J others are G). I'm looking for code that I can relink the database to the front end in runtime, I did try to look in the net but I can't find anything that I can put the password as parameter.
this sample code from Dev is good, but i got an error because the database requires a password.where i can put the password?
Code:
Function fRefreshLinks(NewDbName As String) As Boolean Dim strMsg As String, collTbls As Collection Dim i As Integer, strDBPath As String, strTbl As String Dim dbCurr As Database, dbLink As Database Dim tdfLocal As TableDef
I have a code for linking tables. It works on Access 32 bit.I modify the code for 64 bit (include PtrSafe after all Declares, etc...)But it doesn't work.When I try to chose database in dialog window, my program closed.
Code: Public Function GetDbPath(path_name As String) As Integer Dim i As Long i = MsgBox("The path to database is incorrect" _ & Chr(13) & "Chose new path?", vbOKCancel + vbExclamation) If i <> vbOK Then DoCmd.Quit Exit Function
I am looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless there's an easier way to do it?) - some sample code - if this is possible at all?
I'm looking for some sample VBA code that dynamically creates a link to Visual FoxPro 9 table. Our group has a number of end user FoxPro applications, and as FoxPro is reaching the end of its life in January 2015, we need to replace it.
A lot of the processing we do uses tables with a date embedded in the name, e.g. MyData_20131211.dbf. We'd like to be able to let our users to use Access queries that point to these tables without having to manually create the ODBC link each day. Is there a way to set up a link once, then use VBA code to dynamically change the table it points to?
For example, we set up an ODBC link table to MyData_20131211.dbf, and rename the link table in Access to MyData_Today. Then tomorrow, the VBA code would change the link to point to MyData_20131212.dbf.
An alternative would be to dynamically recreate the link each day.
As the tables are large, we don't want to import them into Access if we don't have to.
I have an Access DB with a form that allows the user to select a new backend database. I can connect to the backend and then .refreshlinks but nothing on the form is updated. I have tried requiring the form but that doesn't do anything. I've tried loads of other things, refresh, recalc etc., but nothing updates the open form.
The only way I have managed to get it to work is to close the form and reopen it, then it shows the data from the newly linked backend database.
While it works, it doesn't look good but also there seems to be some problem with it because eventually it reports an error saying "cannot open any more databases" and when clicking OK comes back with "An error occurred while sending data to the OLE server (the application used to create the object" and a whole bunch of other messages.
I think it might have something to do with the fact that the form has a number of MS graphs open on it, but I'm not sure. Also, I can't track down a particular line of VBA code which causes this error.
how to update a form after changing the backend database.
What I'd like to do is have an "export button", where the OnClick event, exports a single table into a new accde file. This would allow the end users to zip this file up and send it to me.
Because of all sorts of stupidity, I have no network capability and must send data back and forth via email.
I have digressed to a replication table that needs to be uploaded, once data is entered by the users...
Because the files can get relatively large (for emailing purposes), I am trying to figure out how I can get just one table from them vs. sending the whole application file back to me... It's pretty vital that they not be able to edit the table because that could really mess up the process.
So any way to create a new lock file from within the current Db?
Code: Function Write_rstADO_to_CurrdB_Table() 'Assumes you have already setup a DSN to your Server 'Assumes YOURDESTINATIONTABLE is the same structure as your SERVER.TABLE Dim cnnADO As ADODB.Connection Dim wkspDAO As DAO.Workspace
I am creating a database that tracks current projects for my team at work.
Some projects are only due once (e.g., mailed brochures due on 1/1/14) and some are due at scheduled intervals (e.g., status report due monthly, quarterly, etc.)
Ultimately, I'm hoping that my end result will allow us to click on a form and look at what everybody has due that day, in the next 7 days, and so forth.
I currently have 3 tables within a database with student details of three different classes. I need to create a user form that has a dropdown box which I can select a student from one of these tables with a number of text boxes below which brings up all the students details, then once the student has been selected and the correct details are shown then I need to create a button which allows me to move that student from one table to another.
I am trying to move data in one table to a table in an external database and Access isn't playing ball. Supposedly you can use the 'IN' clause to link to an external database but if I put it in then I get 'Syntax error in INSERT INTO statement' and if I take it out it works fine not that, that is what I want. It's syntax seems to be defined like below at the MS website - jet reference???
I've also got a parameter that I'm trying to feed it, that is the path to the database, which is correct! c:helpcontent.mdb
Here's the query INSERT INTO CONTENT_CLASSES IN '"+ @DBDir +"' ( PARENT_ID, CLASS_NAME, STATUS_ID, KEYWORDS, PRIORITY, LAST_CHANGED_BY, LAST_CHANGED_DATE, MULTIPLE_CONTENT ) SELECT A.PARENT_ID, A.CLASS_NAME, A.STATUS_ID, A.KEYWORDS, A.PRIORITY, A.LAST_CHANGED_BY, A.LAST_CHANGED_DATE, A.MULTIPLE_CONTENT FROM CONTENT_CLASSES AS A;
I was wondering is there a way to open up another database from within a database? Not to combine but just to open another one up instead of looking for it on a drive....
I've made an access project (ade) and stored the admin with password in the ade file. When I open a new database I can use the import feature to import all tables with definitions and data from the ade file. All other objects are being locked.
How can i disallow tables from my mde to be imported into another access application?
I am getting this error message: "Unexpected Error from External Database Driver" when I try to import some .dbf files into Access. I just did some quick research, and it was suggested that I remove or rename the borland driver. Not sure how I feel about attempting that, plus, where do I get a new borland driver once the old is removed/renamed.
