Using external database for excel application.

[lemaao]

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:

    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.

 

[CalcSux78]

I think there's an issue with your SQL string. This article may help on ADO connection methods. There's 4 pages of info. (I think pg 2 of 4 is where you should start for this issue)

THT