Replies

Personally, I can’t think of one. It depends on why you need to change the default, but you may have to go with a custom Import/Export form where you can set the directory you want. It will probably require some VBA or macro coding.
Regards,
Kirk

Just an idea: Instead of opening a new query instance, why not just work with the form’s recordset via the RecordsetClone?
Regards,
Kirk

I use a product called Speed Ferret. Couldn’t reverse engineer (efficiently) without it. However, the publisher stopped updating it at Access 2003, and I haven’t tried it with later Access versions. All the same, if you have Access 2003 or older mdbs, this will save you a LOT of time.
http://www.moshannon.com/speedferret.html
Regards,
Kirk

Joop, I use the following statement to update combo boxes and lists:
.Rowsource = .Rowsource
With the various versions of Access I work with, this is the only reliable method I have found to update a combo box or list. This requires a little VBA programming. You need to run the line whenever the event occurs that changes its underlying source data. For example, if I have a list with 2 combos and number 2 needs to get updated after something is selected in 1, I will put the rowsource=rowsource statement into the onClick event of Combo 1, i.e., combo2.rowsource=combo2.rowsource.
Hope this helps,
Kirk

Hi! It sounds like the source data is actually in a character format rather than number. You could do a conversion from character to number and store the result in the target field. That way, the data in the target will sort correctly. The conversion only happens at import time. The fact that the data in the source has a leading zero probably means it is formatted as text, probably in a fixed width field.

Regards,
Kirk

When an activity/listing is added to the database (in table 3,) it is given a creation date within the details table(4). The activity/listing has a life span of 240 days. I would like the database to generate a report to say which agent needs communication for which activity/listing based on Day1, Day15, Day32, Day63, Day95, Day129, Day161, Dayetc. The report to generate each day needs to break into which agent, related activity/listing and form(s) of communication. The report needs to be exported to an Excel book, one sheet for each form of communication…

If this can be done with the database, its creation is worth pursuing.

Kirk, Thanks for your time!!

Hi, Jack. Once you have things normalized, it would be relatively simple to keep track of the age of a listing by using a query with a calculated field (let’s call it Age) that is the CurrentDate – ListingDate. You can use the query as a source for reports, using whatever filter you like on the Age. You can even use it for whatever forms you might build, and use a filter just to see “current” listings, i.e., those that are no more than 240 days old. That way, you are using only one table for all listings, regardless of whether they are active or not. This is conceptually simpler. Of course, you might run into speed problems when you have very many old listings and that is where the second table of only old listings might come into play, but, just my opinion, it is better to use one table.
Another tip: If you will have multiple users accessing the data, give thought to using SQL server on the back end and MSAccess for the user interface. In my experience, when you have more than about 5 users all hitting an Access database, it will trash itself about once every few months. Then again, maybe I am just unlucky. All of the stuff I have done for the past 10 years has been with Access for the UI and SQL for the data backend, and the phone calls for “corrupted data” have dropped to zero. I know it is another layer of complexity, but, compared to normalization, it is pretty simple.
Enjoy!
Kirk

Hi, I took a quick look at the mdb you posted and noticed that the data needs to be normalized. You can get some good information by searching for Data Normalization on the Inet. As an example, I am attaching some modifications I did to your database structure. I dealt with only the agents and all offices tables. I noticed that office phone numbers were in both tables, i.e., the data was being repeated in multiple tables. So, I removed the office-related fields from the agents table, leaving only the officeID. I then set up a One to many relationship between the office table and the agents table, linking them by the officeID. With this setup, an office can have one or many agents assigned to it. If your agents can be associated with multiple offices, then the setup would be different. I think you will need to do quite a bit of normalization to make this data interact correctly. Sorry, there isn’t a quicker fix. To get you started, here are some tips:
1. Think of each table as a collection of discrete entities (entity = record) with a set of attributes (fields) that apply only to that individual entity. For example, if the entity is an agent, and there are multiple agents in an office, then the address of the office does not really apply uniquely to any one agent. Instead, use the OfficeID to link an agent to an office record in another table.
2. Eliminate redundant data. In the original tables, the office addresses were repeated in the agents table and in the office table. They really only need to be in one. Makes it much simpler and more efficient to update the information.
Hope this helps a little. Normalizing data is not something you learn overnight, at least not most of us. A good way to get started, after doing some online research, is to take a look at the sample Northwinds database that MS supplies with Access. It helps immensely to see a working example. Anyway, that’s all I’ve got for now. Good luck.
Kirk

Morning. I have a issue where I’m trying to delete records that match a certain pattern.
The code I’m using is this:

Code:

cnnDBServer = New ADODB.Connection
cnnDBServer.Provider = "Microsoft.Jet.OLEDB.4.0"
cnnDBServer.Open(strDBPath & "XYZServer.mdb")
strSql = "DELETE tblClientFileDetails.* FROM tblClientFileDetails WHERE (((tblClientFileDetails.FileName) Like 'XyzClient*'))"
cnnDBServer.Execute(strSql)
cnnDBServer.Close()
cnnDBServer = Nothing

The process doesn’t delete any of the files. If I replace the strSql with:
“Delete tblClientFileDetails.* From tblClientFileDetails” It deletes all the records so i know the code works. Also If I paste the original sql into MS Access, it deletes the matching records.
I’m coding this in Visual studio 2010 and connecting to an external Access database.
Does anyone see why this doesn’t work?
Thanks,
Scott

Hi, Scott. This typically happens when you are querying an SQL table, but you seem to be accessing an MDB. In any case, you might want to try using % for a wildcard.
Regards,
Kirk