Advice about a current Excel Project 2

[JasonEnsor]

Hi Guys,

I have been working on upgrading a system for some colleagues, all was going well except that now I am being told they need to allow multiple users to utilise the system at the same time.

I have prototyped a system that uses Excel with userforms. It allows the user to insert customer information and view it based on criteria i.e by date, customer surname etc. The reason I am using Excel is 1) The company doesn't support Access which would have been ideal as they need a database really. 2) Excel is where my strengths lie.

I am not a big fan of sharing workbooks, in the past it has caused the current users to lose data, and for the workbooks to become corrupted. The only way I can see around this is to use multiple workbooks and then merge the data in to the main workbook. The big concern I have here is ensuring that each row is assigned a Unique Id (wouldn't a database be just great for that). The reason for needing the Unique Id is that currently when the user searches for data I load the information on to a listview control, I use the Unique Id as a way of finding that row of data in the spreadsheet to allow the updating of the record.

To top it off I am due to leave my current position early Jan, so would ideally like to get this system built and tested, so I am looking for some ideas on how best to approach this.

I have considered using ADO to push and pull data from the main worksheet, or the use of csv files that are imported in to excel.

Does anyone have any ideas on a good approach at making this system work.

Each customer will have multiple rows of data, so the customer id can not be used as a unique id.

Many thanks in advance, I can happily clarify any information that is needed.

Regards

J.

 

[Andrzejek]

In Excel, isn't a Row Number a Unique Id for every row...?

Have fun.

---- Andy

 

[JasonEnsor]

The problem i have using the row number is that when i load the data in to the listview control i sort the worksheet based on the users criteria. I do this so that the data is loaded in to the listview in a sorted state. So what might be on Row 1 originally might end up being row 9 if the user changes criteria.

Regards

J.

 

[SkipVought]

hi,

Yes, I'd consider using ADO to manage the data in a separate back-end "database" workbook, where the front-end workbook, would act as the gui.

You would not have the same features as an actual database, but you might be able to manage the integrity of the data better in this way than using a single shared workbook, which I, too have reservations about.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JasonEnsor]

Thanks for the advice Skip, i have little experience with ADO, so i will have to read up on that a bit more. I think using ADO would make life easier for me as i could then use SQL to populate the listview instead of using the vast amount of If statements that i am currently using. It might help clean the code up also because at the moment my code is a mess.

Thanks Again

Regards

J.

 

[PHV]

I'm with Skip: don't use a shared workbook but a backend data storage.
You may consider SQL Express (free).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

ood luck! Post back whenever you need help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You don't need access to create and manage access databases. "mdb" file is native JET database format, you can create it, add tables, relations, indexes and users using ADOX library. The database can be updated using ADO. Both libraries can be used in excel vba project. So you can:
- create and administrate the database from excel with ADOX,
- allow users to work with data from their excel files with ADO.

combo

 

[JasonEnsor]

Thanks for the suggestions guys, I am building a quick prototype that I can use for testing purposes to make sure I am able to pull data in and out of the main worksheet whilst querying it with VBA and SQL.

So far I can open the connection, query data and close the connection. I have the following statement trying to update a row but I keep getting an error.

'SupportBookings is the tab name in the external workbook, LastName is the column I want to update and PK is the unique id I am using to reference the row

sSQL = "UPDATE [SupportBookings$] SET [LastName] = 'Ensor' WHERE [PK] = '1';"

I have used the connection code below to view data in the spreadsheet

Option Explicit

Public oConnection As ADODB.Connection
Public oRecordSet As ADODB.Recordset
Public sSQL As String
Public tableName As String
Public sFilePath As String

Public Sub OpenConnection(sFilePath As String)

    Set oConnection = New ADODB.Connection

    With oConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties").Value = "Excel 8.0; HDR=YES"
        .Open "Data Source = """ & sFilePath & """"
    End With
End Sub

Public Sub CloseConnection(oConnection As ADODB.Connection)
    oConnection.Close
End Sub

Public Sub BuildSQL(tableName As String)
        sSQL = "SELECT [PK]FROM[" & tableName & "$] WHERE[LastName] = 'Bloggs' AND[ModuleCode] = 'ABC124';"
End Sub

Public Sub CreateRecordSet(oConnection As ADODB.Connection)

    Set oRecordSet = New ADODB.Recordset

    With oRecordSet
        .Source = sSQL
        .ActiveConnection = oConnection
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open
    End With
    
End Sub

Public Sub DisplayResults(oRecordSet As ADODB.Recordset)

    Do Until oRecordSet.EOF
        MsgBox oRecordSet.Fields(0).Value
        oRecordSet.MoveNext
    Loop
    
End Sub

I then run this by using 2 buttons on a userform just to check it is all working nicely

Option Explicit

Private Sub CommandButton1_Click()
    Call OpenConnection(sFilePath)
    MsgBox "Connection Open"
    
    Call BuildSQL("SupportBookings")
    Call CreateRecordSet(oConnection)
    Call DisplayResults(oRecordSet)
    
End Sub

Private Sub CommandButton2_Click()
    Call CloseConnection(oConnection)
    MsgBox "Connection Closed"
End Sub

Private Sub UserForm_Initialize()
    sFilePath = "C:\Users\J\Desktop\Bookings.xlsx"
End Sub

If someone could point me in the direction as to where I am going wrong with my update and maybe give me an example of how to insert a new row then I think I can crack most of this quite quickly

Regards

J.

 

[Andrzejek]

Just a guess here, but I think your PK is a Number (not a text or string):
[tt]
sSQL = "UPDATE [SupportBookings$] SET [LastName] = 'Ensor' [blue]WHERE [PK] = 1[/blue];"
[/tt]

Have fun.

---- Andy

 

[JasonEnsor]

Hi Guy's

I am slowly plodding through this however I have hit a slight issue on this code

lst is my listview control

The problem seems to be that not all of the fields that I am pulling through have a value in my excel spreadsheet data. This is because they are not all required fields. How would I get ADO to pull through the fields with data in and not crash giving me an 'Invalid use of Null' error.

Out of curiosity how would I set the data type of fields that are stored in my excel workbook, an example of this is a field called student number, should be stored as text in the excel raw data so that when ado pulls it in to my listview it views it as text not a number.

Public Sub PopulateSearchResult(oRecordSet As ADODB.Recordset)
    Dim i As Integer
    
    Do Until oRecordSet.EOF
        lst.ListItems.Add 1, , CStr(oRecordSet.Fields(1).Value)
            
        For i = 3 To 10
                lst.ListItems(1).ListSubItems.Add , , oRecordSet.Fields(i).Value
        Next i
        oRecordSet.MoveNext
    Loop
End Sub

Many thanks in advance for any help and suggestions

Regards

J.

 

[PHV]

To avoid nulls you may use use this syntax:
oRecordSet.Fields(i).Value & ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JasonEnsor]

Hey Guy's

I have made major progress on this today but now I am again stuck on a little query, in my excel data I have 3 columns that can either be a name, required or not required. I am trying to filter these in the sql by use of 3 option buttons named All, Required and Not Required. If all is selected then I want all records to show, if Not required I want those to show etc..

What I have so far is
[Code vba]
sSQL = "SELECT * FROM [" & tableName & "$] WHERE [" & columnHeader & "] = '" & _
searchTxt & "' AND [SupportWorker] = '" & reqStatus & "' OR [Interpreter] = '" & reqStatus & "';"
[/code]

If I use
[Code vba]
sSQL = "SELECT * FROM [" & tableName & "$] WHERE [" & columnHeader & "] = '" & _
searchTxt & "' AND [SupportWorker] = '" & reqStatus & "';"
[/code]
then it works for the first column, I am however trying to check 3 columns and determinee if any of those have the Required or Not Required in them depending on the option button selection.

Many thanks in advance

Regards

J.

 

[Andrzejek]

How about your 3 option buttons:
optAll, optReq and optNotReq

sSQL = "SELECT * FROM [" & tableName & "$] WHERE 1 = 1 "

Select Case True
  Case optAll.Value
    sSQL = sSQL & " AND Whatever..."
  Case optReq,Value
    sSQL = sSQL & " AND Whatever..."
  Case optNotReq.Value
    sSQL = sSQL & " AND Whatever..."
End Select

Of course you will need to fill in the SQL for the Cases

Have fun.

---- Andy

 

[JasonEnsor]

Sorted it, it would seem I had set my Required and Not Required option button to the same value...oops.

The following works for me

sSQL = "SELECT * FROM [" & tableName & "$] WHERE [" & columnHeader & "] = '" & _
                searchTxt & "' AND [SupportWorker] = '" & reqStatus & "' OR [Interpreter] = '" & reqStatus & _
                "' OR [ENotetaker] = '" & reqStatus & "';"

Thanks



Regards

J.

 

[JasonEnsor]

Cheers for the suggestion Andy, that might just make things a lot easier when I am adding in extra search facilities

Regards

J.

 

[JasonEnsor]

Hiya,

Okay this will be my last question of the evening....I hope

So all my data is populated in my listview, when I select an item in the listview the data is then populated in to textboxes to allow me to update the values. The best way I can see of doing this is just to overwrite all values in my excel db relating to that record when the user clicks update. This does mean I would be restoring values that might not have changed, but as I am on a deadline this seems better than trying to code a way of checking for just values that have changed.

My question is I have 22 fields that will be being passed to the excel db file. Now I don't want to write a load of code that might not work and then have to retype it due to my syntax being wrong.

to update one field I could use [Code vba] sSQL = "UPDATE [tableName$] SET [LastName] = 'BLOGGS' WHERE [PK] = 1;" [/code]

Building on that I started to structure the code below, could anyone advise if I am going about it the right way?

[Code vba]

sSQLUpdate = "UPDATE [SupportBookings$] "
sSQLSet = "SET [StudyDate] = '" & .txtDate.Text & "'"
sSQLSet = sSQLSet & "SET [StudentID] = '" & .txtStudentID.Text & "'"
sSQLSet = sSQLSet & "SET [FirstName] = '" & .txtFirstName.Text & "'"
sSQLWhere "[PK] = '" & frmSupportBooking.lst.ListItems(Item) & "';"

sSqlString = sSQLUpdate & sSQLSet & sSQLWhere

[/Code]

Many Thanks

Regards

J.

 

[PHV]

sSQLUpdate = "UPDATE [SupportBookings$] "
sSQLSet = "SET [StudyDate] = '" & .txtDate.Text & "'"
sSQLSet = sSQLSet & "[!],[/!] [StudentID] = '" & .txtStudentID.Text & "'"
sSQLSet = sSQLSet & "[!],[/!] [FirstName] = '" & .txtFirstName.Text & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Youe overall syntax to Update fields is:

[pre]
Update TableName SET
FieldA = 'ABC',
FieldB = 123,
FieldC = 'John',
FieldD = 987
WHERE [PK] = 1[/pre]

So to make it simple:
[tt]
sSQL = "UPDATE [SupportBookings$] SET "
sSQL = sSQL & " [StudyDate] = '" & .txtDate.Text & "', "
sSQL = sSQL & " [StudentID] = '" & .txtStudentID.Text & "', "
sSQL = sSQL & " [FirstName] = '" & .txtFirstName.Text & "'"
sSQL = sSQL & " WHERE [PK] = " & frmSupportBooking.lst.ListItems(Item) & ";"
[/tt]

Have fun.

---- Andy

 

[Andrzejek]

I forgot to add: if you have a string, like student's First Name, you need single quotes around the name in your Update SQL, if you have a Number, like student's ID, then no quotes.

If you have a posibility of the names like O'Brian, or anything with the quote in it, do:
[tt]
sSQL = sSQL & " [FirstName] = '" & [blue]Replace([/blue].txtFirstName.Text[blue], "'", "''")[/blue] & "'"[/tt]


Have fun.

---- Andy