By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Calling specific Database to open

P: 33
Hi fellas

I have a list of Databases named to reflect the current working month and year. Each month a copy of the past month's database is saved and the name is changed to the present month. For instance last month's database was named DBS0808, but this month's is named DBS0908.

I have a code attached to a button in a security database that opens another database when the button is clicked. My issue is that I am trying to get the button to open the current DBS database when it is clicked. So when I click on the button this month, it should open DBS0908. When I click it two months from today, it should open DBS1108.

Below is my attempt:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDBS_Click()
  2. ' Open a database
  3. Dim fileStr As String
  4. fileStr = Format(Date, "MMYY")
  5. Const ACCESSEXE = "msaccess.exe"
  6. Const FILENAME = "S:\Iashare\PRIVATE\DBS\DBS" & fileStr & "MID.mdb"
  7.  
  8.  
  9. Dim strFilePath As String, varAppID As Variant, strShell As String
  10.  
  11. On Error GoTo err_cmdDBOpen
  12. strFilePath = SysCmd(acSysCmdAccessDir)
  13.  
  14. If Len(Dir(FILENAME)) > 0 Then
  15.  
  16. ' Create the text Target path and use CHr(34) to add inverted commas
  17.   strShell = strFilePath & ACCESSEXE & " " _
  18.   & Chr(34) & FILENAME & Chr(34)
  19.   varAppID = Shell(strShell, vbNormalFocus)
  20.  
  21. Else
  22.   MsgBox "Problem Opening Your Application. Contact your DBA", vbCritical, _
  23.    "Database is Out of Action"
  24. End If
  25.  
  26. ' Reinstate the following line once you have finished testing
  27. ' DoCmd.Quit acQuitSaveAll
  28. exit_cmdDBOpen:
  29.  
  30. ' The next line displays the current subroutine and can be deleted
  31. 'If chkViewCode Then DoCmd.OpenModule "Form_" & Me.Name, Me.ActiveControl.Name & "_Click"
  32.  
  33.   Exit Sub
  34.  
  35. err_cmdDBOpen:
  36.   Select Case Err.Number
  37.     Case vbObjectError + 1
  38.       'To see line immediately after the error line, Hit Ctrl+Break keys together then
  39.       'pull yellow arrow to Resume Next (below) then hit F8 key "
  40.       Resume Next
  41.     Case Else
  42.       MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical
  43.   End Select
  44.   Resume exit_cmdDBOpen
  45.  
  46. End Sub
  47.  
Sep 3 '08 #1
Share this Question
Share on Google+
7 Replies


MindBender77
100+
P: 234
What error, if any, are you getting when attempting to open this DB. Also, I've had issues when using Date(). You might want to try using Now() instead. Untested but, just a thought.
Expand|Select|Wrap|Line Numbers
  1. Dim fileStr As String
  2. fileStr = Format(Now, "MMYY")
  3.  
HTH,
Bender
Sep 3 '08 #2

Expert 100+
P: 634
Hi

Two other points

1) Can you Declare a constants with a variable included?? ie.
Const FILENAME = "S:\Iashare\PRIVATE\DBS\DBS" & fileStr & "MID.mdb"

2) Just my preferance, but IMHO I think the DB name should be DBSyymm
This will then sort the databases in Explorer in date order when sorted by name!!

But as previously stated, what is the specific problem ??

MTB
Sep 4 '08 #3

P: 33
What error, if any, are you getting when attempting to open this DB. Also, I've had issues when using Date(). You might want to try using Now() instead. Untested but, just a thought.
Expand|Select|Wrap|Line Numbers
  1. Dim fileStr As String
  2. fileStr = Format(Now, "MMYY")
  3.  
HTH,
Bender
The problem is that the code doesnt generate any errors but it JUST WONT OPEN the specified database when the button is clicked. Basically the button doesnt show anything when it is clicked.
Sep 4 '08 #4

P: 33
Hi

Two other points

1) Can you Declare a constants with a variable included?? ie.
Const FILENAME = "S:\Iashare\PRIVATE\DBS\DBS" & fileStr & "MID.mdb"

2) Just my preferance, but IMHO I think the DB name should be DBSyymm
This will then sort the databases in Explorer in date order when sorted by name!!

But as previously stated, what is the specific problem ??

MTB

Hmmm...you've got an interesting observation for point # 1. The problem is that the code doesnt generate any errors but it JUST WONT OPEN the specified database when the button is clicked. Basically the button doesnt show anything when it is clicked.
Sep 4 '08 #5

MindBender77
100+
P: 234
The problem is that the code doesnt generate any errors but it JUST WONT OPEN the specified database when the button is clicked. Basically the button doesnt show anything when it is clicked.
My only suggestion would be to debug line by line. I believe the problem most likely resides in the file name constant. It would seem that access can't find that DB and does nothing. Try trapping the file name to make sure its in the correct format and exists.

Bender
Sep 4 '08 #6

NeoPa
Expert Mod 15k+
P: 31,347
Try adding the following line after line #18 and see what you can see in the Immediate Pane (Ctrl-G to open and select Immediate Pane) :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print strShell
Sep 7 '08 #7

P: 33
Hi

Two other points

1) Can you Declare a constants with a variable included?? ie.
Const FILENAME = "S:\Iashare\PRIVATE\DBS\DBS" & fileStr & "MID.mdb"


2) Just my preferance, but IMHO I think the DB name should be DBSyymm
This will then sort the databases in Explorer in date order when sorted by name!!

But as previously stated, what is the specific problem ??

MTB
Thanks Mike. Apparently you had the right idea all along, and it eluded me somehow. Since I had "Const" infront of FILENAME, and the value of FILENAME includes a variable string, the resulting value of FILENAME was nonexistant. VBA couldnt interpret it as exisiting. Debugging the code line by line, I realized that the value of FILENAME was empty. So I took out the "Const" from infront of FILENAME and voila!!!...problem solved!!

Oh and I declared FILENAME as a string.

Thanks for all your input!
Sep 12 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.