Replies

Thanks for the reply, however, if I only include the * at the end, the query will only find strings beginning with, rather than ‘anywhere within’.

Randy

Thanks for all of the replies to this post.

Legare, your function seems to work very well for me. I wish I had the VB experience to whip up a solution like that, although I’m getting into VBA a lot in Access now.

Thanks again!

Randy

Damon,

I’d love to give that a try, but am unfamiliar with them. Can you give me some pointers?

Thanks,

Randy

Thanks for the reply Hans, but none of those numbers work either. Here’s the function… maybe it’s something that I’m doing wrong. I can’t seem to trap the error number – have tried to display it in a msgbox and tried to debug.print it, but it doesn’t seem to generate one.

Function AppendCashControlExcel()
On Error GoTo AppendCashControlExcel_Err

””DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 8, “CashControl”, “C:BCCRDatabaseCashControl.xls”, True, “ALL!”
DoCmd.SetWarnings True

AppendCashControlExcel_Exit:
Exit Function

AppendCashControlExcel_Err:
If Err.Number = 3022 Then
MsgBox “One or more records were omitted from the import because they had ” & _
“the same ‘Ref ID’, ‘Total’, and ‘GL TransactionDate’.” & vbCrLf & vbCrLf & _
“You may have attempted to load the prior day’s file again. Ensure that you ” & _
“have replaced the prior ‘CashControl.xls’ file with the current one.”, , “Processing Error”
Resume AppendCashControlExcel_Exit
End If
MsgBox Err.Number & ” ” & Error
Resume AppendCashControlExcel_Exit
End Function

Thanks,

Randy

Thanks Francois, but I’m actually not trying to determine the password. I already know it – I’m trying to use the Sub provided on a database that has a password that I created. I want to know if the Open method can be provided a known password so it will not result in a run time error.

Randy

MACROPOD – Thanks! That’s one thing that I didn’t think of, which worked perfectly. I was able to retreive everything but one Sheet, which must be what was causing the problem. I’ll remember this for future reference.

Thanks to everyone else who replied to this post too.

Randy

In my post, above, I said “Another button, with “Me.FilterOn = False” turns the filter off.” I changed that to “DoCmd.ShowAllRecords” with the same result. Then I changed it to:
DoCmd.ShowAllRecords
DoCmd.ShowAllRecords
… and it works fine. That still doesn’t answer my question. And why should I have to enter this command twice to make it work?

Thanks

Thanks, Douglas.

Thanks for clearing that up for me Mark.

Randy

I just stumbled on the answer, and am posting it in case someone else needs it, now or later. If one is manually exporting to Excel from Access 2000, an option in the dialog box called

Charlotte,

Thanks for the reply, and sorry about the confusion on the fields, there are many there but came accross differently. Anyway, I even tested by changing the field names to a,b,c,d,e,f… ect. and tried to export it to every flavor of Excel, with the same results – the information in the fields crossfoots, but is not in the correct fields. Strange thing is that I can copy the entire table and paste to Excel manually, and it’s fine!

I need some Access Headache Pills….

Thanks,

Randy

Mark –

Thanks. I did this in my very early days of Access, and Date will work fine, but it doesn’t solve my problem. I had also tried Me.Repaint before, and just now again, but that doesn’t do it either. The odd thing is that after I pass the field and then back tab to it, I can tell that it puts the date in focus because it makes a 1/2″ black bar in the field – if I press F2 at that time the date become visible.

Or, if I just tab past the field and save the record, the date is written to the record.

Why oh why won’t my date show in the first place?

Thanks,

Randy

Ok, I have several sites that have installed Access 2000. The users are not very PC friendly. Each site has many databases that have either been enabled in, or converted to Access 2000. I need to find out what the ‘state’ is of each database.

I suppose that they could try to create a new query, form, etc. and see if it let’s them, but that wouldn’t be conclusive evidence that it’s an enabled database since someone may have given them a database that prohibits that. But especially, I emphasize that they are not PC friendly; walking them through this for each database will be very time consuming.

I thought that their might be a way to tell programatically whether a database is enabled or converted. I thought of creating a module with the test and either having them import it to each database (I could have them select the database and export it from my utility), or perhaps perform the test directly from my test database.

I can’t find out if there even is such a property/test that I can use.

Thanks for your help.

Randy

FYI: I learned from GHUDSON at http://www.forumco.com/jmhare/ that I could use chr(34) rather than using quote marks which solved my problem.

Thanks,

Randy

Thanks for your reply. I will elaborate somewhat.

The entire process is broken up into several steps, all performed with it’s own respective button. I want to have the user complete some of the process, then make a copy of the database with a different name, where they will complete the process a bit differently than on the original database.

I currently have some code that creates a new empty database, and copies all of the objects to the new database. The problem with that is that References (the dirty word) need to be set up on the new copy. I don’t want to encumber my users with that task, so I figure that since making a copy of the entire database preserves the References, that would be the way to do.

I hope this clarifies what I am looking for. I thought of creating a utility with VB6.0 that the user could open from the database that would store the current path and mdb file name, and prompt the user for the new copy name. The user could then shut down the database and press the button on the VB form to make the copy, followed by code to automatically re-open the first database to complete the rest of the steps.

But I was wondering if anyone out there had a better idea.

Thanks again,

GHH3RD
Randy