Modules & VBA :: Determine File Format Without Opening Database (Using Access 2007)
Jul 15, 2014
Using Access 2007, can I return a value for CurrentProject.FileFormat WITHOUT opening the database?
I'm looking for a way to determine the file version of a given Access database without actually "opening" the database in Access (I don't want it to ever be visible at all or in any way).
So to open a database in Access 2007 "without opening it," I'm using:
Code:
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:Path-ToFile.accdb")
From here, there are at least two different "version" indicators that can be used, as in this example:
Now, if you run this code on an Access 2002/2003 .MDB file, you will get:
Code:
4.0
09.50
If you run this code on an Access 2007 .ACCDB file, you get:
Code:
12.0
09.50
Note two important factors:The db.Properties("Version") seems to be returning the JET version from what I can see in research. This is NOT what I'm after.
The db.Properties("AccessVersion") returns THE SAME VALUE for an '07 ACCDB as it does for an '02-'03 MDB, since Microsoft never updated it, apparently.
Now.... the one way that I have found that ACCURATELY describes the version of a given access database file is to use:
Code:
Debug.Print CurrentProject.FileFormat
...as this will always return, for example, 2.0 for an Access 2.0 .MDB file; 10 for an Access 2002/03 .MDB file; and 12 for and Access 2007 .ACCDB file (and there are others; these are AcFileFormat bitmask constants as described in Access support).
BUT, this CurrentProject object doesn't seem to be available (so far as I can see) when using the "open without opening" method described above.
SO, is there any way to return the FileFormat value without opening the database visibly? (Merely basing it in the extension isn't right either.... that would be easy but not accurate as several formats use MDB.
Question: Is it possible, using VBA, to determine the actual Excel file type without opening the file?
I receive data files from other departments. Seems like every time someone changes their download structure, I end up with file types that do not match the download extension (example: xlm file with a xls extension). The files can't even be opened because of this. I think I can fix it if I could figure out how to determine what the file type really is.
I am on a deadline today and when I went to open an Access 2007 (I abhor the fact that I am forced to use 2007) database, it will not open. It gives me the error message Unrecognized database format [file path].
I have tried creating a brand new database and importing all objects from old to new but I get the same error message.
I have a standard shared Access DB application (i.e. central BE on the network, multiple users running their own copy of the FE on their local machine) Has been in production for a while, no major issues.
New user has been added in recent weeks. My error logger has picked up an error he hit earlier today - doesn't appear to have been fatal (he hasn't come to me with a problem, suggesting he just "OK"d the error prompt and continued on - I need to confirm this with him though...)
Error was 3343 "Unrecognized database format 'C:TempMyDBMyDB.accde'."
Can see it was tripped on a particular form which queries certain data into a subform (so he was obviously able to open the FE and connect to the BE fine; it was several steps down the road where the error has occurred).
Is there a way to open an Access Database from a batch file or VBS file wait for 20 seconds and then close the Access file. Preferably I would like the Access file to run in either invisible or minimized mode. I would like the Access Database to close again after the 20 seconds has elapsed.
(The reason for this is so that the Database can refresh itself from Sharepoint on another users machine so that the excel reports on his machine are up to date).
Just as the title describes, I can't seem to find a solution to be able to link the front-end to the back-end tables through the common file dialog when the backend is an accdb file that is password encrypted.
See the example attached. Here it should work just fine per the Article at :
http://support.microsoft.com/kb/181076
I've found it works fine without a password encrypted back-end but not with a password encrypted back-end.
Anyone have any ideas? It would be nice to use this feature in Access as well as automating the relinking.
How to generate an export specification file by using Access 2007. Many instructions says click on "Advance" while exporting a text file manually but unfortunately i can not find that "Advanced" option in my access software.
Had a combobox so the user could choose between two data entry formats.They don't want the combobox, they want the user to enter data into one or the other textbox - and that choice to trigger the format.Two groups of text box - group 1 has a single text box - group 2 has three text box. When the user selects a text box and types the first character into it. This triggers locking out the other group choice and enables the <Validate and Edit> command button.
If the user backs out (deletes) the text in a text box.It basically makes both groups available again and it disables the <Validate and Edit> command button.Basically, if the text box Change event counts a character, it changes the text box Tag to "Bust". Then it calls a common routine that checks both text boxes.The choice won't take place if the textbox gets focus. It changes if a text box has 1 or more character typed in.
Code: Sub WhosOnFirst() ' two groups of Required field - if one textbox in a group has a character entered first ' that group becomes the Format - enables the <Validate and Accept> button that will take the next setp
I am trying to program a button on my ms access form to open up an excel file.. So far the simplest code ive found online was from URL....
<code>
Private Sub Command57_Click() Dim xlTmp As Excel.Application Set xlTmp = New Excel.Application xlTmp.Workbooks.Open "C:Excel1.xls" xlTmp.Visible = True
[code]...
However the code doesn't work, any way to open a file from within access?
How to search for a file using the dir function, however, i need to search for a file from a main folder which may have several sub folders.
I am having no luck so far - i understand there is a recursive search facility but im not sure how i can get this to work with what i am trying to achieve.
This is the code i have for the dir function:
If Len(Dir("as-tamworth-50midlands.qa$TamworthLaminate C of CsCircuit Foil" & CofC & ".")) = 0 Then MsgBox "This file does not exist" Else MsgBox "Yippee" End If
I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
I am writing code to Refresh table links. I only want to refresh the Access table links and ignore the Excel Table links. Is there a way to determine in VBA if the table link is pointing to an Excel file?
"how to COMPACT the DB by introducing delay of 10 seconds and then close the DB".In the Database, I'm able to accomplish the "Compact" the database using the function below.
Function Compact() SendKeys "%(FMC)", False End Function
As my DB is quite huge, the Compact action takes around 10 seconds to complete.Now, i would like to Close the Database after Compacting the DB. I tried including "DoCmd.Quit" in the function. The commands in the function, closes the DB but the Compact function doesn't seem to have executed as it needs 10 seconds to complete.
Function Compact() SendKeys "%(FMC)", False DoCmd.Quit End Function
how to introduce this delay of 10 seconds and then close the DB.
I have designed a database which I intend to split for multi-users to access from one front end icon on a shared folder at work. I have designed a form bound to cmr record table and on it a subform to another table where cmrs activities will be saved. The form has buttons to and blank controls.
User can create a new activity entry by typing into the blank controls and pressing the save button which saves to the activity table. the edit button extracts a selected records details (selected on the subform) from the activity table and populates the blank field for a user to edit and then hit save to save changes. The delete button deletes a selected record from the activity table. I intend to have multi-users either accessing, viewing and a possibly editing the same customer at the same time. The simultaneous viewing is essential but the simultaneous editing, though not desired is inevitably going to occur.
What I would like to know is:
1. Can you lock an individual record in a table or does the whole table have to be locked. E.g If Colleague 1 is editing Cmr A's record in Table1 can he lock it so Colleague 2 can view and edit Cmr B's record in Table1
2.Can Colleague 1 access/read Cmr A's record in Table1 to retrieve details toe the form controls if Colleague 2 is viewing or editing Cmr A's record in Table1
3. If record lock is possible, how can I initiate it in my example code below.Edit activity record Code
Code: Private Sub Edit_A_Click() 'Get Data to text box control With Me.R_P_Data_P_Subfrm.Form Me.txtrID = !rID Me.txtrID.Tag = !rID Me.txtrefNo = !refNo Me.cmbrpc = !rPC
I've been developing in Access 2002-2003, and now have Access 2007. I can open a 2002-3 .mdb database OK, but when I click on the Main Menu buttons, nothing happens -- they do not work. So I cannot access any of the existing Menus and Forms. In fact, none of the previously existing databases I tried will open/close Forms using the existing buttons. Any ideas?
I have an appplication that reads information from Access Database, it uses ADO (msado15.dll) to open a database using Connection15::Open( ). I am unable to open Access 2007 database using this method. I get the following error: "Microsoft JET Database Engine, Unrecognized database format". Is there something I am doing wrong or I should be aware of? Does msado15.dll support Access 2007 databases? Thank you in advance.
There's possibly a very simple answer to this but it's been boggling my mind all morning and I can't find any answers on the net.
I'm opening a db on a network that was built with Access 2003. When I open it with Access 2003 on one pc it opens fine and I can edit it. But when I open it with Access 2007 I get a message saying that's it's opened the db in read only mode and that if I want to edit it I have to save a copy.
I tried opening the db exclusively but still no luck. Is there a reason why it's doing this and how do I get around it?
I started quite simple, just wanted to read to node MsgId. This is the code I use:
Private Sub ReadXML_Click() Dim aDoc As DOMDocument60 Dim aNode As IXMLDOMNode Set aDoc = New MSXML2.DOMDocument60 aDoc.async = False aDoc.Load DLookup("C:Sample.XML")
[Code] ....
Whatever I try: //MsgId or MsgId the results is "No nodes". How to read to XML.
Besides reading one node, the next challenge will be iterating the PmtInf node. There can be 1 in the XML file but also 50.
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?
I created a database for my Dad's farm and it was all going well until I have been away for a while. I ensured the DB was backed up properly before I left but he didn't know how to back it up himself apparently so now we are in trouble.
Anyway, one day he went to open the DB and got the "unrecognized database format" message and the file doesn't open at all. The DB was created in Access 2013 and has only ever been used in 2013 (though my Dad uses runtime as he doesn't have a full license like me).
There is a couple of months worth of data which would cause a real headache if it was lost so fixing the file is the best outcome if at all possible, I tried the "compact and repair" from inside access without the DB opened but got the same message. I noticed that there is 3rd party software online claiming to fix all access files but they sound a little too good to be true and a good source of infecting my computer with a virus.
I want to open files from a networklocation from VBA, only normal users don't have access to that location (and should preferably not get it).
I instead of using shell("explorer.exe filepath") to open files using windows standard app for the file, which needs userpermissions to the path the file is located, for the currently logged in user.
I'm trying to use CPAU in combination with Shell, because using CPAU you can pass a user/password that has permission to open a process
The only problem is that the filepaths have spaces in them and that is Always a bit of a tricky thing to tackle.
The last command start explorer, but doesn't open the file (just as the first line I posted, it opens Explorer, but not the file. Must be something with the way I pass the filename?)
In Access 2007 there a way to send a report to a file you create from the information in the form?
Right now my code for the command button is:
Dim strMyPath As String Dim strMyName As String strMyPath =" EBEBMainDigital Sender " strMyName = "Permit " & Me.[Permit Number].Value DoCmd.OpenReport "File Current Permit", acViewPreview, , "[ID] = " & Me.[ID] DoCmd.OutputTo acOutputReport, "File Current Permit", acFormatPDF, strMyPath + strMyName, False DoCmd.Close acReport, "File Current Permit"
And that works perfect for sending the current report to the Digital Sender Folder... but I'd like to be able to send it to a certain county folder within the digital sender folder, based on what is listed for the county on the form. same for municipalities. and then, have it create a folder based on the name inside the form..... how to lead it through the path of the folder through the given information on the form, or how to make it create a folder.