I used to know how to get the path for the current database by running a query. I forgot how to do it. I am using Access 2003. I have tried querying the msystem tables without success. I have searched forums and the web without success. Anyone? Thanks!:)
View RepliesHi all
How can I change the output string in the command below to simply save the document to the same folder that the DB resides??
DoCmd.OutputTo acQuery, "qryAwardsExportWord", "RichTextFormat(*.rtf)", "C:dataawards.doc", True, "", 0
any help is appreciated
I have something like this
Code:'sets generic workspace Set ws = DBEngine.Workspaces(0) 'sets target database from workspace ******NOTE hardcoded path****** Set db = ws.OpenDatabase("E:Status ReportstatusReport_Test.mdb") 'sets Recordset to deliverablePercentComplete sql statement for target database Set rs1 = db.OpenRecordset(deliverablePercentComplete)
is there a way to replace the path in the set db call, basically it is the current database that is being worked on, thus it is the current database that is opened, is there a way to say something like...
set db = ws.OpenDatabase(currentDB)
I've got a database with several users and we've had two people recently who rather than creating a shortcut to the db on their desktop copied it and then proceeded to enter their data there instead of into the one on the network.
I would like to be able to add a field on the first page they see when they open it with the complete file name / path information. I know how to do it in Word by adding a field to a header or footer. However, I can't seem to figure out how to do it in Access.
My hope is that if I can display the path and then include a dialog box when the database is opened warning users to verify they have the correct version before they enter any information.
Alternatively, is there a script I could write that would have the db check that it's the V drive copy not a C drive copy?
Any and all help will be greatly appreciated. Thank you.
Charis Wilson
Denver, CO
I am using Access 2010 in a split database. The intent is to place the back end and associated linked files on a server and the front end on work stations for each user. The typical user will just be searching for information in the database not altering it. My current code re-links the tables once the path is known i.e. I have to hard code it into the global constant. Is there a way in VBA or utilizing VBA to read/write to an ini file (for example) that the UNC path would automatically be updated to a global constant in my current code that can be updated automatically based on where the back end is installed.
Global Const BE_DATABASE = "192.168.2.3FMCENFMCTABLES.accdb"
Global Const BE_PASSWORD = "***********"
Global Const BE_DATABASEPATH = "192.168.2.3FMC" 'used to open files
Public Sub CreateDatabaseLinks()
On Error Resume Next 'new
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim td As DAO.TableDef
[code]...
Hey guys,
How can I change the tables' splitted file path via vba?
Ex.: c: ables.mdb -> c:ew path ables.mdb
thank you.
Hi All,
I have been trying to figure this out for ages but I cant seem to get the code right or find any info on it.
In my database i have a mail merge on a button click which creates a new merged word document. I then need a way of when the user saves the document in word, bringing back the file path and putting it in a table called tblHistory with the correct customer ID in there too.
Any help appreciated
Thanks
Alex
I am attempting to split an Access 2007 database. My company has two locations. From my location we are remote connecting into the server. While down there they are connecting directly. When I split the database, people in my location can use it fine. When people down there use it, they get a not valid path error.
This is because the network drives are mapped differently. I have been reading that the solution is to use the UNC for the back end file path.
I am new to Microsoft access. I have a database moved from another computer. it is split into the backend and the front end..
However, when i click on the forms in the front end, I get an error message. It is trying to use a path from the old computer, to the database, but its on a different computer.
So, this brings me to my question, how do i change the path, to the new location
Hi there,
I want to link an excel sheet to my database but I dont want to link the location with a drive letter but instead linking the network address. How can this be achieved?
In addition, I want to startup application with a command line in a access macro and used this line but it doesnt work.
\Tgsshs11data.nl.post.comocc&sqa$OPS_ProcessesReportsAwkward est.txt
In general how can you work in access with network path strings?
best regards,
Paul.
Hi there,
I want to link an excel sheet to my database but I dont want to link the location with a drive letter but instead linking the network address. How can this be achieved?
In addition, I want to startup application with a command line in a access macro and used this line but it doesnt work.
\Tgsshs11data.nl.post.comocc&sqa$OPS_ProcessesReportsAwkward est.txt
In general how can you work in access with network path strings?
best regards,
Paul.
I have a report in Access 2010. The report contains some images in the main report area and in sub reports. I have The queries that feed the report and subreport contains the name of the pictures that are loaded into its image controls. I added the following code to my form in order to control what image is displayed for each record. Id like the form to work even if the database is moved as long as the subfolder and photos are moved along with it (thus using relative path).
Code:
Private Sub Report_(Current)
Dim ImagePath As String
Dim ImagePathInt As String
Dim ImagePathPlanV As String
[code]...
The problem seems to be that the routine doesn't seem to initially recognize the path. I have a blank photo in the folder containing all the images called NO PHOTO.jpg. If I open the report in design view and browse to this image in the picture parameter of any one of the image controls and run the report, then everything works perfectly even if I close and reopen the report. The report shows the right pictures even in the subreports.
The problem is when I close the database and reopen it, then the link is lost again, forcing me to do the process of browsing for the NO PHOTO image again. I already try to do an open form procedure to automatically reset the picture property, but although it resets the picture to the picture I set (I verify this by setting a different picture instead of the NO PHOTO) it does not make things work correctly as it does when I manually browse for it in design view. I also try to run the code above under the Report (Open) instead of the Report (Current) but that didn't work either.
Till now I managed to publish and save all in C:Reports. What I'm trying to do is Save to path specified in Path. DB attached.
Private Sub PrintAll_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyPath As String
Dim MyFileName As String
[Code] ....
Just wanted to confirm which is the best way to create an automatic back up of a access DB. (Split DB, backing up the BE - Access2010)
Firstly I was thinking of just creating a batch file to copy the file to a new source directly with a task scheduler set up.
Ideally I'd like it to run daily and have the date of the back up added to the end of the filepath.
In the access form I want to add a button with a special functionality.
After clicking "Select file" button special window should be open (or something else). It should give user a possibility of files locating (doc, pdf, rtf, txt ...).
After selecting the file and confirm the choice in the table "File_location" in database path to the file should be saved .
I maintain an Access 97 database system with a number of front ends and two different back ends (for security reasons). The structure is replicated in different directories as there are training, development and back-up copies as well as the live system. I want the menu page to display the name of the front end that is opened and the path and name of the two back ends, referenced from the current database (front end). To do this, I want to read the directory for the current database as opposed to the current directory which may be different. I can't find this in any VBA reference for MS Access 97; although I used to use the equivalent in Excel using the Get function - as I remember it.
Any help?
Gibbo
I am creating an order processing system and am using 2 tables
OrderHeader
OrderLines
The OrderHeader table includes the number of orderlines on the order.
I want to ensure that when a user completes the order lines they don't up in a line number greater than the number of lines and want to programatically get the number of lines from the order header table.
The form links to the ID of the order header table by using a combo box, which works fine and I can even display the number of order lines in the combo box.
I don't know how to use an ADODB recordset to query the current database.
I can't use something like :
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:filesdatabase.mdb"
which i see all over the place, because I don't know where on the PC this will be running on it will be stored.
There must be a simple way to find out a value from a field within a recordset of the current application database?
Any ideas?
I have a module that I am using to verify that file paths in my table point to valid jpg files. The table has grown quite a bit since I first created the module and it has gotten to the point where it's taking too long for the module to execute. I'm in the process of trying to change the record set for the module from the table to a stored query procedure but it's turning out to be a little tricky for me. When I execute the following module, I'm not getting any error code, it just doesn't seem to do anything at all. The bits in red are the parts I've changed. Before that the module executed as I expected it would.
Code:
Sub TestIt2()
Dim strFileName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("QryUpdateRevisionHistory")
[code]....
please see post #4
View 3 Replies View RelatedI'm having difficulties creating a sql statement which updates data in another Access database from a current database.
I'm able to do an insert and delete statement just perfectly, but not an Update. :confused:
For example, here is a dummy sample (which does not work):
Update tbl_test1 IN '\C:Tempabc.mdb', tbl_test2
set tbl_test1.Name = tbl_test2.Name
Where tbl_test1.ID = tbl_test2.ID
I tried a couple of variations, however, I keep getting an error.
Also, I don't want to create a database link (due to the sheer complexity of my project, which I'll spare you the details..)
Does anyone know how to do this? I searched the forums and came up with zilch! =(.. Help??!
I am currently looking for coding on my current database's login screen. It functions correctly but i want to be able to add a user lock out feature to prevent people from constantly trying to hack users passwords...
View 1 Replies View RelatedI 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.
View 1 Replies View RelatedCurrently, I have a database situation in which I am working in one database with data. If this data does not belong in this database I need to be able to take specific fields from the record and insert it into either a new table in a different database I have, or have it copied to a blank form that would accept it.
View 1 Replies View RelatedWhat 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
[Code] ....
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.