I am after a script to change the linked table paths like the following.
It will only ever be the path that is changing not the file name and only ever linked tables
Code:
for each table in tabledefs
if table.path = c:Testing* then table.path = w:Testingfilename
if table.path = c:Jobs* then table.path = w:Jobsfilename
if table.path = c:Quotes* then table.path = w:QuotesNewfilename
next table
I'm trying to change the table links to a password protected BE DB. I found an example online, which I adapted to my needs. When I set it up to fail to find the normal BE it seems to work as intended until it gets to the line "Tdf.RefreshLink". Then it crashes with a 3031 "Not a valid password" error. The code is:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim NewPathname As String
[Code] ....
I can't find any references or example to relinking a password protected table.
Is it possible to hide the locations of linked tables that we see when using linked table manager? I want to have a desktop Access FE database link to a BE database which is on an intranet server. I know this works because I have done it, but for security reasons can the link address be hidden? That is really a just-in-case scenario, because I can establish the links when opening the main form, and delete them when closing it. It's also easy enough to keep users out of the ribbon and the navigation pane.
If that can't be done, is it possible using VBA to map the database location to a network drive letter, as you can do with Computer - Map Network Drive (Win 7), without knowing what drive letter is the next one available?
I have a question that I have a Microsoft Access database (.accdb) front-end/backend split and I want to give the database to my company client. As we have the different path for the backend/frontend linked. I want some code that will popup if the database location is not found and popup with the dialog so the user then select the backend and it would be ready and there is no need to popup each time the database open, it would run once it did not find the last linked path.
Also I have tried the code of Dev Ashish URL.... but unfortunately it would ask everytime to refresh the table links and I only want to run the process of linking tables when the database start and the linked path not found.
I need to know how to change the Access variable names programmatically.
I have a large Access database, with hundreds of variables that need to be shortened to 8 characters since they are to be imported into a SIR database, which does not allow variable names longer than 8 characters.
Because the variables have to be unique and yet still identifiable, I have to write code that will do abreviations. For instance:
Neglect Aphasia Conversion Baseline
Neglect Aphasia Conversion Drug
Neglect Aphasia Conversion Fluency
Neglect Aphasia Conversion Composite
will become:
N A CV BA
N A CV DR
N A CV FL
N A CV COM
Would something like this be done in a macro?
And once the variable names are abreviated, how would they be put back in the database table?
i have 2tables,table1=productid,ProductName,Qty table2=Productid,productName,Qty,date(). I want to be able to backup Records programatically daily from table1 into table2 OnClose.But i do not want duplication of record in the same day. If changes occurs in table1 after backup,table2 should be updated Programatically.
View 1 Replies View RelatedIs it possible to change the properties of a linked table from Number to Text and have this remain constant using VB code. If so, how can I do this?
View 1 Replies View RelatedI need to update the names of my ODBC linked tables in my Access database, how can I do this without causing issues with my queries/reports?The current linked tables are to a SQL View on a database called mcsrm_live, and called e.g. vwDamagesReportNew
The new SQL views that I need to link to are identical in structure and content and on the same SQL server but different database - forkdw and are called e.g vw_R_Damages
Is there a straightforward process to do this without affecting the queries and reports in my Access db?
I have a number of databases that use ODBC connections for working with linked tables from a SQL database. The source SQL data has been moved to a new server with new sa credentials. I have updated my local ODBC connections to use this new information. However, when I added numerous tables to the databases I checked the box to save the password so that it wouldn't prompt me or other users every time I wanted to use the Access database. Is there a way in Access to change this stored password? So far I haven't been able to find one.
View 1 Replies View RelatedI have a access 2007 file. with a table with a yes/no field. When I link the table to another db using lnked table manager, the yes/no display is not a checkbox (as set) but is turns into textbox.
View 1 Replies View Related I use this bit code to look at a table for the path for pdf file to go into
Code:
varFolder = DLookup("Folderpath", "pdfFolder")
this looks at the table pdffolder
what I would like to do if possible is for this to look at the first line in the table but I want to use this part of the code for 4 or 5 different paths Is it possible to modify the code so when I use it 5 times each code looks at different lines in the table
I have recently been working on the code below and I have neally completed what I want to do.
Private Sub H_Enter()
Form!H = "hyperlink#E:WORKSPEC PROJECTWORKSPECS" & Form!S & Form!PC & Form!extension & "#"
End Sub
What I want to do is have the file location (bit I have bolded) stored in another form or table and have the code read it from there as the file locations may change, rather than having to enter it for each line I just want to enter it once and then as and when it changes I just change the one line but it changes all the hyperlinks
hope this makes sense.
In the past I have only done data input into Access databases that have already been created. They have been simple text fields only. I was hired on contract to finish data input for a database that serves to catalogue material resources. The database itself was supposed to have been completed by a former contractor but I'm discovering bits and pieces that weren't finished. I don't have any background in IT or coding.
So, this database eventually has to be shared in a "locked" format so that other entities can view our resources (like a catalogue) without changing them. My next step is to add images to each of the items being catalogued (there are about 100 images). The former contractor began a table labeled "Pictures" in which there are 2 columns: PictureID and PicturePath, the latter being a path starting with C:. The issue is, his picture paths include the hard drive name so that it looks like this:C:HardDrivePicturesPictureID.jpg
I need the database and its images to be share-able on other computers. And I need to use relative paths to connect the images to the database so I'm not tremendously increasing the size of the database.does the location/path of the database need to be changed so it can be shared, and if so how do I do that?
Second question: I have all the images I will be using in a folder on my desktop; how do I save them in a "fixed subdirectory" of the database so that they get moved with it whenever it is sent out to another user?
If I change the database name as I'm working on it (I've been saving a new database each day, so that if I screw up something I can go back to the most recent working one) will that mess up my picture paths?
I am trying to export a query or table to a location that the user selects. So each time the export button is clicked the folder will change. But I want to be able to select the folder.
The code I have below is saving but not to the folder I want it to.
Private Sub CommandBtn_Click()
Dim fileSelection As Object
Dim strPath As String
Set fileSelection = Application.FileDialog(4)
With fileSelection
.AllowMultiSelect = False
If .Show = True Then
[Code] ....
I have a FE database with all the forms, reports, etc. and my BE DB with all the tables is in the same directory as the FE. My problem is when users are not in the office and opens the FE data base they get an error that the path to the linked tables are not available.
My question: Is there a way to make these FE links to the BE dynamic. I really don't want various FE with paths linked to the BE.
I would like to export a table as a text file to a user defined location.
I have it mostly working, but not exactly as I would like. I'm stuck on the user defined location.
I have a Form that contains a subform and two command buttons.
The subform contains the table I want to export as a text file.
The text file has to be comma delimited, no qualifiers.
I have the transfertext command in VBA that works perfectly:
Code:
DoCmd.TransferText acExportDelim, "My Specification Name", "MyTableToExport", StrDirTemp & "input_" & StrPName & "NameCode" & StrDIAUnFormatted & "d" & ".txt", False
What I'm stuck on is the filepath. The file path changes everytime. So I would like to have either the open dialog box (I've tried many different versions that I found on the web.) or to search by the account name for the folder and place the text file in there.
Here is one that is closely working how I want it to:
This is a function that I found, that opens a dialog box for the user to select the folder location. It works, but I can't seem to get it to work properly.
It prompts, the location, then once you select it and press ok. It will add the folder name to the full file name, and place the file in the default root path. Not the selected folder path.
So in the end it will look like this:
D:1_MainMyFolderName_MyTextFileName.txt
I'm somehow stuck on getting to seperate the file path from the file name, so you it look like this:
D:1_MainMyFolderNameMyTextFileName.txt
Code:
Dim MSg As String
Dim SelectedDir As String
Dim SelectedDirFinal As String
Dim SelectedDirName As String
Dim StrFolder As String
[Code] ....
I think it should be something very easy, that I just need a pair of fresh eyes to look.
I've tried the Fileobject, FileFolder method, but can't get the quite work properly.
I've also tried wildcard methods as well:
StrDirTemp = Dir(StrFolder & StrPName & "*", , vbNormal)
But keep throwing up blanks.
I need some simple code that will copy an Excel file or a table in Access to a specific location on an FTP server. I would think this would be a very simple task, but I have yet to find any sample code that is *simple*. I have seen lots of code that requires downloading this dll or that mda, but the examples don't work. There must be something built into MS Access 2010 that will allow a file to be uploaded to an FTP site.
All the variables are known:
The FTP location (it never changes)
The FTP Username and Password (they never change)
The destination folder on the FTP site (it never changes)
The File type (it never changes)
The File name (available from the form in Access from which this will be executed)
I can either produce an output file, then copy it to the FTP site, or I can export the table directly to the FTP site with the file name for that day.
This seems to be a very simple task with no simple solution. Currently I am using an FTP app to get the file to the FTP site, but I would like to automate this. The process that creates the output file is already automated, so I would just like to add this to the existing code as its own module.
I'm looking for a simple command on the idea of:
DoCmd.FTPFile, acExel, TableName, FTPFilePath, FTPFileName, Username, Password, acUpload
just made all that above up and none of it is a real function/command in VBA, but is just the kind of thing I'm looking for.
I would think that since I can download and XML file from an FTP site that it should be child's play to upload a simple file to an FTP site, but I can't figure it out.
I have linked tables in my db at the moment that rely on user dsn connections to an SQL server. I've been reading about DSNLess connections and want to try convert what i have to have permanent DSNless connections, but the code I've found doesn't appear to be working.
I've removed server specific details where i felt necessary, but when running the code i have it in place.
Code:
Public Sub RefreshODBCLinks()
Dim connString As String
Dim db As DAO.Database
Dim tb As DAO.TableDef
connString = "DRIVER=SQL Server;SERVER=<database ip address>;DATABASE=<
[Code] .....
I need a way that I can simply have a User click a button on a form of the database and it will do automatically create a zip file of the entire database and start Outlook and attach the zip file and send it to me.....
View 4 Replies View RelatedHi
I need help to create a table programmatically in access. I have 2 tables. For e.g.
Table A
Field1 Field2
abcde 12345
bbbbb 54321
and Table B
Field1 Field2
abcde 99887
bbbbb 76543
As you can see Field1 of both tables are the same. I just need to create a Table Table C such that
Table C
Field1 Field2 Field3
abcde 12345 99887
bbbbb 54321 76543
Can this be done using a macro? Thanks so much in advance
I have a form (about patients) containing a listbox (list of visit dates) and a subform (showing medications at that date). When you click on the listbox, the subform is re-queried with the selected date as a parameter to show the medications that were in use on that date. This is working fine.
However, when you move to a new record (patient), the subform is initially blank because the listbox hasn't been clicked on yet. This means that it looks like the person isn't on any medications, which is causing some confusion.
I need a way to have the subform show the "top" (most recent) date because that is the current medication list, and the thing most people want to see first. It is always the top of the list, so I would like to do something like this (pseudocode)
Code:
Private Sub Form_Current()
Forms![MedSearch].SelectDateBox.Requery
Send MouseClick Event to line 0 of SelectDateBox
End Sub
Hi
I have a table that when imported from an external program (sage) has a number based property.
To achieve linkage with other tables, I have to manually change the field fom a number to text.
Is this possible to achieve programmatically.
Much appreciated on any advise.
Regards
Paul
I am using Access 2010. How to change the property of a field in a table programmatically.
I have a table in which one field has Required property set to "Yes". I would like to set this property by using VBA code to "No", then add data into a table using a query and re-set the Required property to "Yes".
Is this possible and if so, how can I do it?
Does anyone know how to change all linked tables in a database to unlinked? I want to save a database for offline testing and want to save the tables as flat tables instead of linked. Is there anyway to do this without bringing them all back in unlinked?
View 1 Replies View RelatedHello all...
I have a problem with a single query I have build in Access 2000. Basically, I have a normalised database structure to 3NF. I have a table called Data, a table called Reports and a table called DataReports which cotains the primary keys from both data and reports which are named Data_ID and Report_Id.
Basically, what is happening is I am building a query that returns a record from DataReports using the Data_Id field as criteria as this is held on a form. I create a one to many relationship from the Reports table to the DataReports table using a link from one Report_Id to the other. I save the table and it works fine...once only!! The query is used to feed a list box on a form. The form loads correctly but when I go back into design view to make a change to something else on the form and then go back to form view, I get Data Type Mismatch error. When I look at the query, Access has changed the link from the Report_Id --> Report_Id to Report_Id --> Report_Title which is the next field down in the Reports table. Because all ID's in my database are Autonum's it is trying to link a number to text so I understand why this would not work but I dont understand why on earth access is changing my links. It is very frustrating. Could anyone help me please.
Thank you kindly..
Gareth
I have a split database ,and I need to update the Table default value of a field.Rather than go into the table I would prefer to use a form.I found this code but it wont work,I presume becouse my data base is split
Private Sub UpdateInvoiceReportNumber_Click()
If Not IsNull(Me.txtDefValue) Then
CurrentDb.TableDefs("PaymentsT").Fields("SelectInv oice").DefaultValue = Me.txtDefValue
MsgBox "Default Value has been changed to " & Me.txtDefValue
[code]...