Using Access from Excel

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Hi there,

I'm very new to using Access although I have been able to use Excel to a fairly high level. For a project I am about to undertake, I was looking to combine the two.

I am writing a program within Excel/Visual Basic that completes an application form to be printed and sent out. In addition to this, all information that is captured for the application, I want captured within a database. Previously, a simple Excel spreadsheet was used however it would seem Access would be better suited to this (as my user base is 50+ with potentially thousands of records).

My questions are:

Can my program (which will be used by 50+ users, all accessing it Read Only) be linked directly to an Access database? Therefore any application they complete, the details are svaed directly to the database?

Do I need to have Access installed on ever users pc or can Excel query the database directly without it.

I would like t add Add/Amend functionality to the Excel program, so that records could be created and existing records edited.

Would it be possible with multiple users? Ie, two people access the same record.

Any help would be fantastic,

Andy
 

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Actually scrap that question, I figured out how to return one record (based on the "DownloadTop20" download from the Tutorial) but it's getting it back in thats proving difficult.

If I extracted say, RecordID "115" I want to be able to update that one record, does this make sense?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Having a closer look at things, it seems that my problem is getting the record I have extracted from the .mdb file, back in.

My database table has 7 columns (fields?) in it. when I extract one record it is populated into Worksheet("HUB").Range("E2:K2") with E2 being the RecordID

This is what I have so far...

Sub UpdateRecord()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim NewConnection As ADODB.Connection
Dim RecordSet As ADODB.RecordSet
Dim fld As ADODB.Field
Dim BBTDatabase_Path, Search As String
Dim RecordID As Long
Dim Y As Long

BBTDatabase = Worksheets("Matrix").Range("FileName").Value

RecordID = Worksheets("HUB").Range("Search").Value

Search = "SELECT * FROM StaffData WHERE RecordID = " & RecordID

Set NewConnection = New ADODB.Connection
BBTDatabase_Path = Worksheets("Matrix").Range("FilePath").Value & BBTDatabase

With NewConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open BBTDatabase_Path
End With

Set RecordSet = New ADODB.RecordSet
RecordSet.CursorLocation = adUseServer
RecordSet.Open Source:=Search, ActiveConnection:=NewConnection, CursorType:=adOpenKeyset, LockType:=adLockOptimistic

' INSERT UPLOAD CODE HERE!!!

RecordSet.Update

' Close the connection
RecordSet.Close
NewConnection.Close
Set RecordSet = Nothing
Set NewConnection = Nothing

End Sub
 

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
I'm getting a bit desparate, can anyone help? I'm more than willing to send on the document I have if that would help?
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Andrew,

Try replacing this...
Code:
' INSERT UPLOAD CODE HERE!!!

RecordSet.Update

with something like this...

Code:
With RecordSet
  .Edit
  !Fields("FirstField") = Sheets("YourSheet").Range("E2").Value
  !Fields("SecondField") = Sheets("YourSheet").Range("F2").Value
  '...etc
  .Update
End With

Denis
 

AndrewKent

Well-known Member
Joined
Jul 26, 2006
Messages
889
Hi There,

I spent most of last night trying to figure this all out with some degree of success. I managed to come up with a robust routine but in an attempt to learn more, I've added your code into a routine that I have. The code that is commented out works fine, the code added in comes up with the error message...

Method or data member not found

...for the ".Edit" line...

Code:
Sub UploadRecord()

    Dim DBName, DBLocation, FilePath As String

    Dim DBConnection As ADODB.Connection
    Dim DBRecordSet As ADODB.Recordset
    Dim XLRow As Long, XLColumn As Long
    Dim DataRange As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Worksheets("Find Record").Range("A2").Value = Worksheets("Index").Range("H3").Value
    Worksheets("Find Record").Range("B2").Value = Worksheets("Index").Range("I3").Value
    Worksheets("Find Record").Range("C2").Value = Worksheets("Index").Range("J3").Value
    Worksheets("Find Record").Range("D2").Value = Worksheets("Index").Range("K3").Value
    Worksheets("Find Record").Range("E2").Value = Worksheets("Index").Range("L3").Value
    Worksheets("Find Record").Range("F2").Value = Worksheets("Index").Range("M3").Value
    Worksheets("Find Record").Range("G2").Value = Worksheets("Index").Range("N3").Value
    
    Worksheets("Find Record").Activate

    Set DBConnection = New ADODB.Connection
    DBName = Worksheets("Matrix").Range("DatabaseName").Value
    DBLocation = Worksheets("Matrix").Range("DatabaseLocation").Value
    FilePath = DBLocation & DBName
    
    With DBConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open FilePath
    End With

    Set DBRecordSet = New ADODB.Recordset
    DBRecordSet.CursorLocation = adUseServer
    DBRecordSet.Open Source:="StaffData", ActiveConnection:=DBConnection, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable
    
'    DBRecordSet.AddNew
'    XLRow = 2
'    For XLColumn = 1 To 7
'            DBRecordSet(Cells(1, XLColumn).Value) = Cells(XLRow, XLColumn).Value
'    Next XLColumn
'    DBRecordSet.Update
    
    With DBRecordSet
        .Edit
        .Fields("RecordID") = Worksheets("Index").Range("H3").Value
        .Fields("FirstName") = Worksheets("Index").Range("I3").Value
        .Fields("Surname") = Worksheets("Index").Range("J3").Value
        .Fields("Level") = Worksheets("Index").Range("K3").Value
        .Fields("TeamLeader") = Worksheets("Index").Range("L3").Value
        .Fields("CSM") = Worksheets("Index").Range("M3").Value
        .Fields("RACF") = Worksheets("Index").Range("N3").Value
        .Update
    End With
    
    DBRecordSet.Close
    DBConnection.Close
    Set DBRecordSet = Nothing
    Set DBConnection = Nothing
    
    Worksheets("Index").Activate
    Range("A1").Select

End Sub

Any suggestions?

Andy
 

Watch MrExcel Video

Forum statistics

Threads
1,129,396
Messages
5,636,059
Members
416,896
Latest member
Hozier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top