How to link an excell data to ACCESS (using ACCESS as a storage?)

Hi guys,

I am currently using an excel worksheet as a tool to generate customer related data (such as using excel functions to work out some numbers and also other financial information of each customer). And for each customer my excel worksheet generates a quote (basically a quote on differnt worksheet and hyperlinked to a cell in my master worksheet, so whenever I input a new customer data, it automatically update the figures in the quote worksheet).

But more and more I deal with clients, I found it difficult to save all the data I generate each day in my excel workbook. So I started to wonder if there is any good way to store all my customer's data (sorted in order) either using another workbook or ACCESS??

So, each time I input a custmer data into my worksheet (using it only as a template, not a storage), It will save customers basic info plus their quotes generated using the master workbook on a separate worksheet. And probably using a button on the worksheet or using other methods, I can export the new customers data into ACCESS or other excel workbook (and sorted in a particular order).

Any suggestions? I think to link an excel data to either ACCESS or another WORK BOOK, I will need a VBA code?

Thanks,

How about storing your data in access and retrieving it in excel via marco?

First we need to create a connection string
Public Function ConnectAccess(strPath As String, bConnectOption As Boolean) As Boolean
'On Error GoTo ErrorHandler
Set conADO = New ADODB.Connection
If bConnectOption = True Then
If conADO.State = adStateClosed Then
With conADO

If Right(UCase(strPath), 3) = "MDB" Then

.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=;User ID=;" & _
"Data Source=" & strPath & ";" & _
"Persist Security Info=false;"
Else
.Connectionsting = " Provider=Microsoft.ACE.OLEDB.12.0;" + _
" Data Source=strpath; " + _
" Persist Security Info=False;"
End If

.CursorLocation = adUseClient

.Open
ConnectAccess = True
End With
Else
ConnectAccess = True
End If
Else
If conADO.State = adStateClosed Then
With conADO

.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=;User ID=;" & _
"Data Source=" & strPath & ";" & _
"Persist Security Info=false;"

.CursorLocation = adUseClient
.Open
ConnectAccess = True
End With
Else
ConnectAccess = True
End If
End If
'Exit Function
'ErrorHandler:
'MsgBox Err.Description, vbInformation, "Failed to Connect To MS Access Database"
End Function

and call the function
Public Function RS2WS(rs As ADODB.Recordset, targetcell As Range)

On Error GoTo Err

Dim f As Integer, r As Long, c As Long
If rs Is Nothing Then Exit Function
If rs.State <> adStateOpen Then Exit Function
If targetcell Is Nothing Then Exit Function

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.StatusBar = "Writing data from recordset..."
End With

With targetcell.Cells(1, 1)
r = .Row
c = .Column
End With

With targetcell.Parent
.Range(.Cells(r, c), .Cells(.Rows.Count, c + rs.Fields.Count - 1)).Clear
' clear existing contents
' write column headers
For f = 0 To rs.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = rs.Fields(f).Name
On Error GoTo 0
Next f
' write records
On Error Resume Next
rs.MoveFirst
On Error GoTo 0
Do While Not rs.EOF
r = r + 1
For f = 0 To rs.Fields.Count - 1
On Error Resume Next
.Cells(r, c + f).Formula = rs.Fields(f).Value
On Error GoTo 0
Next f
rs.MoveNext
Loop
.Rows(targetcell.Cells(1, 1).Row).Font.Bold = True
.Columns("A:IV").AutoFit
End With

With Application
.StatusBar = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

Exit Function

Err:
MsgBox Err.Description, vbInformation

End Function

Hi nsumaya,

Thanks for the posting. I am actually very new to VBA. Will you be kind enough to highlight the code that needs to be changed? and also should I copy and paste the code above in 'This workbook' or in the worksheet where all the data is going to be stored?

Little bit more of explanation will be very helpful. Thanks

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Originally Posted by jjin

Hi nsumaya,

Thanks for the posting. I am actually very new to VBA. Will you be kind enough to highlight the code that needs to be changed? and also should I copy and paste the code above in 'This workbook' or in the worksheet where all the data is going to be stored?

Little bit more of explanation will be very helpful. Thanks

Hi JJin, First of all you need to have an ms access database in place. After that you need to create a module open an excel file and press Alt+F11.
1. Click Insert Module, copy and paste functions mentioned above.

This function will retrieve data from ms access

Please Login or Register  to view this content.

AWESOM!! Thanks nsumaya.