-
Feb 10th, 2014, 08:57 PM
#1
Thread Starter
Addicted Member
Open Different Data Source Name
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\BSParking.mdb;"
As you can see the example above it opens BSParking.mdb,
Is there a way to open different file name?
And how can I add a Botton to Open a Explorer and search for that Data source?
New Problem: http://www.vbforums.com/showthread.p...=1#post4614539
Last edited by Cronqvist; Feb 11th, 2014 at 11:08 PM.
-
Feb 10th, 2014, 10:29 PM
#2
PowerPoster
Re: Open Different Data Source Name
You must be new to programming.
Is there a way to open different file name?
Make "BSParking.mdb" a variable and you can put anything there you want.
And how can I add a Botton to Open a Explorer and search for that Data source?
Do some research on Common Controls
-
Feb 10th, 2014, 10:46 PM
#3
Thread Starter
Addicted Member
Re: Open Different Data Source Name
how to make it viable? im totally new Sire
-
Feb 11th, 2014, 05:34 AM
#4
Re: Open Different Data Source Name
dw85745 probably meant something like this:
Code:
Option Explicit 'Drop a CommandButton & a CommonDialog control on a blank Form
Private Sub Command1_Click()
Dim sPathName As String
With CommonDialog1 'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
.CancelError = True
.Filter = "Access Database file (*.mdb)|*.mdb"
.Flags = cdlOFNHideReadOnly
On Error GoTo 1
.ShowOpen
sPathName = .FileName
1 End With
If LenB(sPathName) Then '<-- If sPathName isn't empty
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
End If
End Sub
On Local Error Resume Next: If Not Empty Is Nothing Then Do While Null: ReDim i(True To False) As Currency: Loop: Else Debug.Assert CCur(CLng(CInt(CBool(False Imp True Xor False Eqv True)))): Stop: On Local Error GoTo 0
Declare Sub CrashVB Lib "msvbvm60" (Optional DontPassMe As Any)
-
Feb 11th, 2014, 05:40 AM
#5
Thread Starter
Addicted Member
Re: Open Different Data Source Name
Sir Bonnie West thank you soo much!
but where do i need to put this code? On my main form right?
gonna try wait
i need only 1 command button right?
-
Feb 11th, 2014, 06:16 AM
#6
Thread Starter
Addicted Member
Re: Open Different Data Source Name
I did put your Codes into my Main Form
and add the code into my new added command button, sadly got Error Sir
Compile error:
Variable not defined
Highlighted text: CommonDialog1
Private Sub Command1_Click()
Dim sPathName As String
With CommonDialog1 'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
.CancelError = True
.Filter = "Access Database file (*.mdb)|*.mdb"
.Flags = cdlOFNHideReadOnly
On Error GoTo 1
.ShowOpen
sPathName = .FileName
1 End With
If LenB(sPathName) Then '<-- If sPathName isn't empty
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
End If
End Sub
-
Feb 11th, 2014, 06:58 AM
#7
Re: Open Different Data Source Name
Originally Posted by Cronqvist
I did put your Codes into my Main Form
and add the code into my new added command button, sadly got Error Sir
Compile error:
Variable not defined
Highlighted text: CommonDialog1
I believe my instructions were very clear:
Originally Posted by Bonnie West
Don't know how you've missed that... Anyway, if you still need more directions, see Using the Common Dialog Control and especially the subsection Displaying Open and Save As Dialog Boxes. At some point in the future, you'll probably want to ditch the dependency on that ActiveX control. Let us know if you're seeking a replacement for that.
P.S.
Glad to know you're using Option Explicit!
On Local Error Resume Next: If Not Empty Is Nothing Then Do While Null: ReDim i(True To False) As Currency: Loop: Else Debug.Assert CCur(CLng(CInt(CBool(False Imp True Xor False Eqv True)))): Stop: On Local Error GoTo 0
Declare Sub CrashVB Lib "msvbvm60" (Optional DontPassMe As Any)
-
Feb 11th, 2014, 07:14 AM
#8
Thread Starter
Addicted Member
Re: Open Different Data Source Name
Its working fine =)
I did put a Object CommonDialog and Command Button at my Main Form. When I run works fine
and I can now open any .MDB File
Problem
Run-time error '3705':
Operation is not allowed when the object is open.
CODE Code:
Private Sub Command1_Click()
Dim sPathName As String
With CommonDialog1 'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
.CancelError = True
.Filter = "Access Database file (*.mdb)|*.mdb"
.Flags = cdlOFNHideReadOnly
On Error GoTo 1
.ShowOpen
sPathName = .FileName
1 End With
If LenB(sPathName) Then '<-- If sPathName isn't empty
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
End If
End Sub
Highlighted Code
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
-
Feb 11th, 2014, 12:08 PM
#9
Thread Starter
Addicted Member
Re: Open Different Data Source Name
Last edited by Cronqvist; Feb 11th, 2014 at 11:07 PM.
-
Feb 11th, 2014, 07:20 PM
#10
Thread Starter
Addicted Member
Re: Open Different Data Source Name
Last edited by Cronqvist; Feb 11th, 2014 at 11:07 PM.
-
Feb 11th, 2014, 11:06 PM
#11
Thread Starter
Addicted Member
Re: Open Different Data Source Name
-
Feb 12th, 2014, 04:23 AM
#12
Re: Open Different Data Source Name
Before you try to re-apply the next Connection-String, you should ensure that your FindCon-Connection
is closed (if it was opened before):
If FindCon.State <> adStateClosed Then FindCon.Close
Olaf
-
Feb 12th, 2014, 04:46 AM
#13
Thread Starter
Addicted Member
Re: Open Different Data Source Name
where do i put this code sir?
-
Feb 12th, 2014, 05:19 AM
#14
Re: Open Different Data Source Name
Originally Posted by Cronqvist
where do i put this code sir?
As I said: "...Before you try to re-apply the next Connection-String..."
I'd think that indicates a position above your line which starts with:
FindCon.ConnectionString = ...
Olaf
-
Feb 12th, 2014, 05:33 AM
#15
Thread Starter
Addicted Member
Re: Open Different Data Source Name
this is the cause of a student who didn't take up seriously the subject prola3 and has lots of absent -.-
i got an idea about simple inventory only.and i search for some codes and new ideas
those ideas kinda hard to analyze and turn into a debug huhu
so that is why a simple code from you guys i can't understand where to put it
-
Feb 17th, 2014, 05:46 AM
#16
Thread Starter
Addicted Member
Re: Open Different Data Source Name
-
Feb 17th, 2014, 11:31 AM
#17
Member
Re: Open Different Data Source Name
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\BSParking.mdb;"
Instead of the "BSParking.mbd", type the file name you want to open. Make sure it is in the same directory as your project.
Insert this into the form you want the database to be linked to.
say the form name is form.1
Then in your codes section type:
Form1.Load()
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\YOUR FILE NAME HERE.mdb;"
Tell me if this works
-
Feb 17th, 2014, 08:38 PM
#18
Thread Starter
Addicted Member
Re: Open Different Data Source Name
Sir, I didn't try your idea. Because i don't want to paste the Database on the same drive where the system is.
I want is to open any Database anywhere.
my mainForm has a Listview. In this Listview I can open the 2013.mdb and can open 2014.mdb too this is what I want Sir.
Current Problem
Run-time error '3705':
Operation is not allowed when the object is open.
Code:
Private Sub Command1_Click()
Dim sPathName As String
With CommonDialog1 'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
.CancelError = True
.Filter = "Access Database file (*.mdb)|*.mdb"
.Flags = cdlOFNHideReadOnly
On Error GoTo 1
.ShowOpen
sPathName = .FileName
1 End With
If LenB(sPathName) Then '<-- If sPathName isn't empty
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
End If
End Sub
Highlighted Code
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
Last edited by Cronqvist; Feb 17th, 2014 at 08:43 PM.
-
Feb 24th, 2014, 09:28 AM
#19
Thread Starter
Addicted Member
Re: Open Different Data Source Name
help please. been waiting for someone who will post into this thread.
sorry for bumbing this thread. really need to know the answer for this :'(
-
Feb 24th, 2014, 10:13 AM
#20
Member
Re: Open Different Data Source Name
What you want is a single listview but say you click a button and it displays one database and when you click other button it displays other database?
-
Feb 24th, 2014, 10:56 AM
#21
Re: Open Different Data Source Name
Originally Posted by Cronqvist
help please. been waiting for someone who will post into this thread.
The answer was already given - in my post #12.
Then you asked, where to put this single line of code...
And that was also answered (in my post #14).
I don't know what more you need - if you are too lazy to read the replies you were given carefully,
then you shouldn't wonder why none of the regulars here wants to invest any additional time into
answering even more questions of yours...
Olaf
-
Feb 26th, 2014, 02:23 AM
#22
Thread Starter
Addicted Member
Re: Open Different Data Source Name
-
Feb 26th, 2014, 03:58 AM
#23
Re: Open Different Data Source Name
Originally Posted by Cronqvist
In post #18 you state the following problem:
Run-time error '3705':
Operation is not allowed when the object is open.
And if something is not allowed, when "something else" is still open,
what about when you try to close it then (before you try the line of code
which is causing the error).
I can only repeat - that was answered already in post #12 - and after that
I was even stating, where you should put that line-of-code in post #14.
Olaf
-
Feb 26th, 2014, 11:21 AM
#24
Thread Starter
Addicted Member
-
Feb 26th, 2014, 04:01 PM
#25
Re: Open Different Data Source Name
Originally Posted by Cronqvist
omg im soo sorry im too stupid
i really dont get it sir guys i cant understand
guide please huhu
That doesn't help much - I was not trying to intimidate you "into cowering in the corner" -
we were all Newbies at some point...
But what one can expect from a Newbie is, that you read the posts 12 and 14 again
(try the line I've posted there out in your own code) - and then ask concrete questions
in case something was not working or unclear (with citations of the sentences, "where I lost you").
Simply stating that you didn't understood what I wrote is not enough - I cannot
guess here *what* (concretely) you didn't understand - please be more specific -
and post the attempts you already undertook on your end as code-snippets (with
appropriate comments, on the lines where you got errors).
Olaf
-
Feb 27th, 2014, 02:14 AM
#26
Thread Starter
Addicted Member
Re: Open Different Data Source Name
Modules Connection Code
Code:
Public FindCon As ADODB.Connection
Public FindRs As ADODB.Recordset
Public Sub openCon()
Set FindCon = New ADODB.Connection
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "/2014.mdb;"
FindCon.Open
If FindCon.State = adStateClosed Then
FindCon.Close
End If
End Sub
Public Sub executeQuery(ByVal sql As String)
On Error GoTo errorcon
Set FindCon = New ADODB.Connection
If FindCon.State = adStateOpen Then
FindCon.Close
End If
openCon
FindCon.Execute sql, adOpenDynamic, adCmdText
Exit Sub:
errorcon:
MsgBox Error.Description, vbCritical, "Error"
Set FindCon = Nothing
End
End Sub
Public Sub FindRecordset(ByVal query As String)
On Error GoTo errorrs
Set FindRs = New ADODB.Recordset
If FindRs.State = adStateOpen Then
FindRs.Close
End If
openCon
FindRs.Open query, FindCon, adOpenDynamic, adLockOptimistic
Exit Sub:
errorrs:
MsgBox Err.Description, vbCritical, "Error"
Set FindRs = Nothing
End
End Sub
Public Sub RefreshListview()
Dim Itmx As ListItem
FindRecordset "Select * From Finder Order By Owner"
EmployeeMain.ListView.ListItems.Clear
While Not FindRs.EOF = True
Set Itmx = EmployeeMain.ListView.ListItems.Add(, , FindRs.Fields("Owner"))
Itmx.SubItems(1) = FindRs.Fields("EmployeeNumber")
Itmx.SubItems(2) = FindRs.Fields("IDCount")
Itmx.SubItems(3) = FindRs.Fields("IDStatus")
Itmx.SubItems(4) = FindRs.Fields("Department")
Itmx.SubItems(5) = FindRs.Fields("Acquiredby")
Itmx.SubItems(6) = FindRs.Fields("IssueDate")
Itmx.SubItems(7) = FindRs.Fields("ReleaseDate")
FindRs.MoveNext
Wend
End Sub
Main Form
Code:
Private Sub cmdOpenClose_Click()
Dim sPathName As String
With CommonDialog1
.CancelError = True
.Filter = "Access Database File (*.mdb)|*.mdb"
.Flags = cdlOFNHideReadOnly
On Error GoTo 1
.ShowOpen
sPathName = .FileName
1 End With
If LenB(sPathName) Then
End If
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
FindCon.Close
End Sub
Error
Code:
Run-time error '3705': Operation is not allowed when object is open
Screenshot of Error
this error code is inside of the Main Form
-
Feb 27th, 2014, 03:35 AM
#27
Frenzied Member
Re: Open Different Data Source Name
why you have written the following sub in the module .and opening other connection in cmdopenclose_click event . i am persure following code
is the cause of this issue .
Code:
Public Sub openCon()
Set FindCon = New ADODB.Connection
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "/2014.mdb;"
FindCon.Open
If FindCon.State = adStateClosed Then
FindCon.Close
End If
End Sub
-
Feb 27th, 2014, 03:47 AM
#28
Thread Starter
Addicted Member
Re: Open Different Data Source Name
Sir firoz.raj, you want me to remove that Code from the Module?
I did remove and i get an error.
here,
-
Feb 27th, 2014, 06:26 AM
#29
Re: Open Different Data Source Name
Originally Posted by Cronqvist
Screenshot of Error
Generally, you will fare better when you'd use some kind of indentation on your Code-Snippets -
With indentation it would become perhaps more obvious, that you introduced another error now
(in the LenB(sPathName) check)... aside from that, I've now put the line from my post #12
directly above the line which starts with: "FindCon.ConnectionString =" (as stated in post #14)
Code:
If LenB(sPathName) Then
If FindCon.State <> adStateClosed Then FindCon.Close '<- the line from post #12
FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
FindCon.Open
End If
Aside from that, your helper-code-module is not a bad idea - but if you introduce a globally available
OpenCon-function, then why don't you make any use of it?
That means, that the above code (which now would work), should be replaced by your new
OpenCon-Function which sits in your helper-module...
Your Helper-Module could look like this one here:
Into a Module:
Code:
Option Explicit
Public Con As ADODB.Connection
Public Sub CloseCon()
If Con Is Nothing Then Set Con = New ADODB.Connection
If Not Con.State = adStateClosed Then Con.Close
End Sub
Public Sub OpenCon(mdbFileName As String)
'since Con is a public Var (and DB-Files might change), we always try to close it first
CloseCon
'since we ensured above, that Con is "closed and clean" we can change to a different DB-File now...
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName
Con.CursorLocation = adUseClient
Con.Open '... and finally open the connection on the new File
End Sub
Public Function GetRs(Query As String) As ADODB.Recordset
Set GetRs = New ADODB.Recordset
GetRs.Open Query, Con, adOpenStatic, adLockOptimistic
End Function
Public Sub RefreshListview(LV As ListView, Rs As ADODB.Recordset)
LV.ListItems.Clear
Do Until Rs.EOF
With LV.ListItems.Add(, , Rs("Owner"))
.SubItems(1) = Rs("EmployeeNumber")
.SubItems(2) = Rs("IDCount")
.SubItems(3) = Rs("IDStatus")
.SubItems(4) = Rs("Department")
.SubItems(5) = Rs("Acquiredby")
.SubItems(6) = Rs("IssueDate")
.SubItems(7) = Rs("ReleaseDate")
End With
Rs.MoveNext
Loop
Rs.Close
End Sub
And with the help of the above Module, the Form-Code could be cleaned up and look more like that:
Into an empty Test-Form (Project needs a reference to ADO and a ListView1 on top of it)
Code:
Option Explicit
Private Sub Form_Load()
Dim i As Long, DBNames(0 To 1) As String
'ensure correct settings on a virginal ListView
ListView1.View = lvwReport
For i = 0 To 7: ListView1.ColumnHeaders.Add , , "Hdr " & i: Next
'ensure two Temp-MDB-Paths within a String-Array
DBNames(0) = Environ("Temp") & "\test0.mdb"
DBNames(1) = Environ("Temp") & "\test1.mdb"
'just two small test-DBs are created (from the array-members, within the Temp-Folder)
MakeDummyDB DBNames(0)
MakeDummyDB DBNames(1)
'now we simulate opening (and using) our two different MDB-Files
OpenCon DBNames(0) 'starting with the one at index 0
RefreshListview ListView1, GetRs("Select * From Finder Order By Owner")
OpenCon DBNames(1) 'and now switching over to the File at index 1
RefreshListview ListView1, GetRs("Select * From Finder Order By Owner")
End Sub
Private Sub MakeDummyDB(mdbFileName As String)
On Error Resume Next: Kill mdbFileName: On Error GoTo 0
CreateObject("ADOX.Catalog").Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName
OpenCon mdbFileName
Con.Execute "Create Table Finder(Owner Text, EmployeeNumber Text, IDCount Text, IDStatus Text, Department Text, Acquiredby Text, IssueDate Text, ReleaseDate Text)"
With GetRs("Select * from Finder Where 0")
Dim i&, j&
For i = 0 To 9
.AddNew
For j = 0 To 7: .Fields(j) = "Text " & i & "_" & j: Next
Next
.UpdateBatch
End With
End Sub
Private Sub Form_Unload(Cancel As Integer)
CloseCon
End Sub
I leave it to you, to incorporate and apply the Functions in the Module-Code I've posted, into your own Form.
Olaf
-
Feb 27th, 2014, 09:33 AM
#30
Thread Starter
Addicted Member
Re: Open Different Data Source Name
im really stupid. im trying to install all the codes information you give to me.
i do not know if i got it right or i put them in the right location or with the right brain.
didn't work. got error: argument not optional
i did upload the project to mediafire sir
http://www.mediafire.com/download/sg...nqvist+VB6.rar
-
Feb 27th, 2014, 11:00 PM
#31
Thread Starter
Addicted Member
Re: Open Different Data Source Name
help please
-
Feb 28th, 2014, 04:55 PM
#32
Re: Open Different Data Source Name
I've had only the time, to adapt the code in your small Form1 to the new *.bas-Module-Content:
Form1-code:
Code:
Option Explicit
Private Sub Form_Load()
OpenCon App.Path & "\2014.mdb"
RefreshListview ListView, GetRs("Select " & LVFldList & " From Finder Order By Owner")
End Sub
Private Sub Form_Resize()
ListView.Move 0, 400, ScaleWidth, IIf(ScaleHeight - 400 > 0, ScaleHeight - 400, 1)
End Sub
Private Sub cmdOpenClose_Click()
With CommonDialog1
.Filter = "Access Database file (*.mdb)|*.mdb"
.Flags = cdlOFNHideReadOnly
.ShowOpen
If Len(.FileName) = 0 Then Exit Sub
OpenCon .FileName
RefreshListview ListView, GetRs("Select " & LVFldList & " From Finder Order By Owner")
End With
End Sub
And the new, updated Code which should replace the old one in your *.bas-Module:
(now containing a generic ListView-Fill-routine, which should work with any Recordset-
Content (incl. AutoSizing of the ColumnWidth etc.).
Code:
Option Explicit
Public Const LVFldList$ = "Owner As [NAME], EmployeeNumber As [EMPLOYEE NO], IDCount As [NO OF ID], " & _
"IDStatus As [Status], Department As [DEPARTMENT/OFFICE], Acquiredby As [ACQUIRED BY], " & _
"IssueDate As [DATE ISSUED], ReleaseDate As [DATE RELEASED]"
Declare Function SendMessageA& Lib "user32" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any)
Public Con As ADODB.Connection
Public Sub CloseCon()
If Con Is Nothing Then Set Con = New ADODB.Connection
If Not Con.State = adStateClosed Then Con.Close
End Sub
Public Sub OpenCon(mdbFileName As String)
CloseCon 'since Con is a public Var (and DB-Files might change), we always try to close it first
'since we ensured above, that Con is "closed and clean", we can change to a different DB-File now...
Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName '... and finally open the Con on the new File
Con.CursorLocation = adUseClient
End Sub
Public Function GetRs(Query As String) As ADODB.Recordset
Set GetRs = New ADODB.Recordset
GetRs.Open Query, Con, adOpenStatic, adLockOptimistic
End Function
Public Sub RefreshListview(LV As ListView, Rs As ADODB.Recordset) 'works generically
Dim i As Long
LV.View = lvwReport
LV.FullRowSelect = True
LV.ListItems.Clear
LV.ColumnHeaders.Clear
'transport the FieldNames into the Header
For i = 0 To Rs.Fields.Count - 1
LV.ColumnHeaders.Add , , Rs(i).Name
Next
Do Until Rs.EOF
With LV.ListItems.Add(, , Rs(0))
For i = 1 To Rs.Fields.Count - 1: .SubItems(i) = Rs(i): Next
End With
Rs.MoveNext
Loop
Rs.Close
'ensure an AutoSizing of the LV-Columns
For i = 0 To Rs.Fields.Count - 1: SendMessageA LV.hWnd, &H101E, i, ByVal -2: Next
If LV.ListItems.Count Then LV.ListItems(2).Selected = True ': LV.ListItems(1).Selected = True
End Sub
Hope that helps.
Of course your other Forms (which still want to use functions from the other Module-Code)
will then complain when you start-up your project - so it's perhaps better you make a copy
of your entire project-folder - and then remove the other forms, until only Form1 and the
EmployeemdlCon-Module remains.
Then work your way slowly back, introducing one of your just removed forms after the other,
adapting their code to the new capabilities (especially the new GetRs-Call, which is quite
powerful when used smartly).
Just two more things which came to my mind, whilst looking over your project.
Try to remove these huge Image-Backgrounds (HD-resoultion, 1920x1080) from your Forms
Picture-Property - a smaller image of e.g. 1280x720 would be fully enough - and then
apply those BackGrounds on Form_Load directly with one line of code (per LoadPicture)
dynamically - this would reduce the size of your executable (which is currently about 17MB)
significantly - just keep the BackGround-image as a JPG-resource (1280x720) in a SubFolder
below your App.Path and load it from there.
The other thing is, that two of your forms look nearly identically (the add-form and the update-form).
It's the same set of Controls which visualize your current Recordset-Fields - and thus you could
encapsulate these Controls within a UserControl - that's what these things are useful for -
this way you would have to place and write the basically same code only once...
Olaf
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|