Link to Access database

[Raghava@rock]

Hi Need help to link excel file which will be place in individual desk tops of the employees to one database which is placed in Shared drive.


Thanks

Raghava Sharma

 

[vijaySharma]

Hello Raghava,


You would need to place the below code in the excel file on a module and then add your code.

[pre]

Option Explicit
Public cnn As Object
Public rs As Object
Public strSQL As String

Sub OpenDB()

Set cnn = CreateObject("ADODB.Connection")
cnn.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersveekayDocumentsNorthwind.accdb;Persist Security Info=False"

cnn.Open

End Sub

Sub getData()

OpenDB
Set rs = CreateObject("ADODB.Recordset")
rs.cursorlocation = 3 'Client

strSQL = "Select * from products"
rs.Open strSQL, cnn
If rs.RecordCount > 0 Then
Do While Not rs.EOF

ActiveCell.CopyFromRecordset rs
Loop
Else

End If
Set rs = Nothing
End Sub

[/pre]

The above example will link to the Northwind sample access database and fetch all the records from the Products table.


HTH


~VijaySharma