• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

'Using Excel As Your Database'

J

Jeff Slavin

New Member
Using Chandoo's code in his artile 'Using Excel As Your Database'. It seems the database connection never closes, so need help figuring this out.

I changed his code so my dashboard.xlsx is reading data from a separate data.xlsx file. Here is the code I'm running (open the database, read the data, close the database). This code is in my dashboard.xlsm file:

Code: 
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
 
  'Setup connection with the data.xlsx file
  cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
  ActiveWorkbook.Path & Application.PathSeparator & "data.xlsx"
  cnn.Open
 
  strSQL = "Select Distinct [Name] From [Sheet1$] Order by [Name]"
 
  rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
  If rs.RecordCount > 0 Then
       Do While Not rs.EOF
            MsgBox rs.Fields(0)
            rs.MoveNext
       Loop
  Else
       MsgBox "I was not able to find any unique Names.", vbCritical + vbOKOnly
       Exit Sub
  End If
 
  rs.Close
  rs.CursorLocation = adUseClient
  cnn.Close
 
  Set rs = Nothing
  Set cnn = Nothing

After I run this code, if I navigate to my data.xlsx file and double click it to open, i get the 'File In Use' message and can only open as read-only. It appears Excel/Windows thinks the data.xlsx file is still in use even though I've closed all the connection in my code. Any idea how to make sure data.xlsx gets closed completely after the above code runs to ensure others can update data.xlsx?

Thanks in advance!!
Jeff Slavin
jmslavin@gmail.com
 
Last edited:
Hi Jeff ,

I use Excel 2007 , and I am not able to reproduce your problem ; I have uploaded the sample file from the blog post , and separated the data into an external file data.xlsx ; when I open the main .xlsm file , click on the Update Drop Downs button , the drop downs are populated ; when I make the selections , and click on the Show Data button , the correct data is displayed.

Now when I close the .xlsm file , and double click on data.xlsx , it opens without any problem.

Can you download these two files , copy them to any unused directory , and see if you face the same problem ?

Narayan
 

Attachments

  • Excel-As-Database-demo-v1.xlsm
    43.3 KB · Views: 35
  • data.xlsx
    936.3 KB · Views: 34
Hi Narayan,

Thank you for your quick reply. Do not close the dashboard.xlsm file and try opening the data.xls file (after the code runs). This should give you that message about Read Only. I want to be able to leave the dashboard.xlsm file open but completely close out the data.xls file so others can update it.

Thanks again for your time!
Jeff
 
Hi Jeff ,

Can you try this closeRS code , and run it before opening data.xlsx ?
Code: 
Public Sub closeRS()
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
    If cnn.State = adStateOpen Then cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub
Narayan
 
Hi Narayan,

I tried that code, but even after running that (and leaving dashboard.xlsm open), when I go to my data.xlsx file, it gives me the 'In use' message (can only open as read-only).

Only if i close the dashboard.xlsm file does it appear to 'release' the data.xls file and I can open it for write access.
 
Hi Jeff ,

I have tried it in Excel 2007 , and it works ; I ran the query , and after the results were displayed , I ran the closeRS code ; now , with the .xlsm file still open , I double-clicked on the data.xlsx file ; it opened without displaying any message.

If you are using some other version of Excel , then I'm afraid I cannot help.

Narayan
 
If it helps, let me describe what I'm trying to do, maybe there are other options... But i'd really like to use SQL since that is a lot faster then anything else I've used.

I want to have a dashboard.xlsm file saved on my work server that multiple people can open. I want that dashboard.xlsm file to be able to read/write to the data.xlsx file. Since multiple users might have the dashboard open and clicking buttons to update the data file, I need to ensure that the data file is closed out after every read or update so any other user can also read/update the same file. If Excel/Windows thinks the data file is still in use after the first user updates it, then user 2, 3, 4... any other user will not be able to update.
 
Hi Jeff ,

It may be the version which is the problem ; probably others can verify and confirm.

However , I am confused by your description of what you intend to do :

In the Chandoo blog post , the .xlsm file is merely retrieving data to be displayed to the user ; it is not writing to the data.xlsx file. If you say that multiple users will be updating the data.xlsx file , will they be doing it through the .xlsm file , or will they be manually entering data directly into the data.xlsx file ?

If it is the latter , then the .xlsm file should open the database in read-only mode ; if it is the former , then , since the user will not interact with the data.xlsx file , there should not be any problem.

Narayan
 
Hi Narayan,

I think we might have to wait for other users/ninjas to respond. Here is an example of what I'm trying to do (hopefully this clears things up).

I have 2 files saved on a server where multiple people can access:
1) Dashboard.xlsm - manages the data tables in Data.xlsx
2) Data.xlsx - holds all of the raw data tables

Let's say we have 3 users. User 1, opens Dashboard and clicks a button that uses SQL to update one of the tables in Data.xlsx (then closes the connection to Data.xlsx)

While User 1 still has the Dashboard.xlsm open, User 2 opens Dashboard.xlsm on their computer and clicks a button to update Data.xlsx file.

Here is the problem: When user 1 clicks the button on Dashboard.xlsm to update Data.xlsx, it does not appear to close Data.xlsx once the update is complete (if i try to manually open data.xlsx after I run the update code in Dashboard, I get the 'read only' message).

I was mutiple users to be able to have the Dashboard.xlsm file open at the same time, and all be able to click the 'Update' button in Dashboard to update the Data.xlsx file. But if when the first user clicks the button and it updates the data file (but does not appear to close the connection), when the 2nd user clicks the button on their version of the dashboard file, it will give us the 'file in user' error and not update the data.xlsx file.
 
Hi Jeff ,

As I understand you , your .xlsm file will be updating the data.xlsx file with input from multiple users.

In this case , the macro has to do the writing ; if the file is designated as a Shared Workbook , it should still work.

The error you mention happens because :

1. The file data.xlsx is not a Shared Workbook

2. A second user is attempting to open the file manually ; if the code does the same , it can properly carry out the write to file ; repeated open and close operations will not be required.

Narayan
 
Narayan,

You are sweet. I'll try this suggestion out (shared workbook) and let you know how that works... Will have to wait until Monday when I'm back at work so I can try to multiple user updates on the data.xlsx file.

Appreciate all your time and help!!!
Jeff
 
Ok I got it working for the most part, but I have to close the main .xlsm file for the updates in Data.xlsx file to show. Anyone know the cause of this (having to close the main file for the SQL updates to show in the data file)?

Excel 2010

- Also open to other methods of reading/writing database data to other sheets if there is a better tool (ADO, DAO, etc...)
 
Hello,

I love the idea of using Excel as the database. I have tried to do this, but cant get past this code

Everytime I click on the command button that run this code, , I get this error

Run time error --2147352571 (50020005);
type mismatch

it points to this erorr on the line

cmbcustomer.AddItem rs.Fields(0)

The data is on the tab called data
the command button is on the view, but I seem not to get it

Many thanks for any help


Private Sub cmdUpdate_Click()

strSQL = "Select Distinct [Cust Name] From [data$] Order by [Cust Name]"
closeRS
OpenDB
cmbcustomer.Clear

rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbcustomer.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Name.", vbCritical + vbOKOnly
Exit Sub
End If

'----------------------------
strSQL = "Select Distinct [Cust #] From [data$] Order by [Cust #]"
closeRS
OpenDB
cmbcustno.Clear

rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbcustno.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Cust #.", vbCritical + vbOKOnly
Exit Sub
End If
'----------------------
strSQL = "Select Distinct [Apply to] From [data$] Order by [Apply to]"
closeRS
OpenDB
cmbinv.Clear

rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbinv.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Customer Type(s).", vbCritical + vbOKOnly
Exit Sub
End If



End Sub
 

Attachments

  • SLedger.xlsx.xlsm
    71.4 KB · Views: 10
Narayan,

I used the worksheet you gave me , it works. Thanks a lot.

Now, I want to show only those customer's invoices. So, I put a where clause, where using the variable xtype. The xtype has a value of "I"

The code did work, when I hit the button "get customer invoices". I think there is an error when I used the variable.

strSQL = "SELECT * FROM [data$] WHERE [data$].[Type] = " & xtype _
& " and [data$].[Cust #]='" & cmbcustno.Text & "'"



Can you help me pinpoint the error.

Attached is the file too.

Many thanks.

Conrad
 

Attachments

  • Conrado_Natac_SLedger.xlsm
    73.3 KB · Views: 6
Hi Conrado ,

Sorry , I have seen your post just now ; I'll reply.

If possible , always start a new thread , so that everyone notices it immediately.

Narayan
 
Thanks a million Narayan,

I was looking at it and noticed that you made an apostrophe enclosed like this " ' ". Is the ruling all variables to be enclosed like that?
 
Conrado Natac said:
Thanks a million Narayan,

I was looking at it and noticed that you made an apostrophe enclosed like this " ' ". Is the ruling all variables to be enclosed like that?
Click to expand...
Hi Conrado ,

You are right. When in doubt , always try with literal values instead of parameters ; when you use parameters , the SQL string should be the same as when you use literals.

Thus , when you try in your specific case , the normal SQL string would be :

SELECT * FROM [data$] WHERE [data$].[Type] = 'I' and [data$].[Cust #]='GL 888'

Now , when you use parameters , you should see that your strSQL after its assignment is identical to the above. Only then will your query work correctly.

Since the variable xtype contains the character I , the single quotes on either side have to be added. Similarly for the variable cmbcustno.Text

Narayan
 
You must log in or register to reply here.
Back
Top