Using external database for excel application.

lemaao

New Member
Joined
Jun 25, 2015
Messages
15
Hi all,

I have developed a rather large application with VBA that we use at work. The problem with this is that the database is now in the excel application, and that means that no more than one person can use the application at a time. This is normally ok, but I thought I would try to fix this issue.

I want to use an external database, and just use excel/vba as a front to manipulate data in the database.

The application can now register new cases/rows in the database, search for old ones and change them, send e-mails, print statistics etc. I would like it to be able to keep doing this.

I have considered two options.

1. A CSV database.
2. An Access database using SQL(ADODB).

I am having some issues with choosing what database to go for, so I have been going back and forth, but I find it hard to find examples/information on what I need to do.

I guess I will start at the first issue I am having:

I want to open a database, find the last row used(case that has a reference nr), extract that number and +1 so that I can generate the next reference number for a new case.

Can anyone help with either the SQL(ADODB) or the CSV way of doing this?

This is the SQL code I have tried:
Code:
    Dim sqlConnect As ADODB.Connection
    Dim sqlRecord As ADODB.Recordset

    Dim cn As Object
    Dim rs As Object

    Set sqlConnect = New ADODB.Connection
    Set sqlRecord = New ADODB.Recordset

    sqlConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=L:\Nett\Driftssentral\DRS\databaseDRS.accdb;Persist Security Info=False;"

    Set cn = CreateObject("ADODB.Connection")
    cn.Open sqlConnect

    strSQL = "SELECT [Varsler Navn] FROM [tblDatabase] WHERE [RefNr] = L50208 ;"

    Set rs = CreateObject("ADODB.RecordSet")
    rs.ActiveConnection = cn

    rs.Open Source:=strSQL, ActiveConnection:=cn, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText

    InputForm.refnr.Text = rs![Varsler Navn]

    rs.Close
    cn.Close
    Set cn = Nothing
I have just tried to find something to put in a text box basically. Not working....

I will take any advice on what database to use or tips on how to make any of the ways work ;)

Thank you for your help, and I apologize for the lengthy post.
 

Forum statistics

Threads
1,082,309
Messages
5,364,420
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top