How do I write from Excel to Access?

[ISUTri]

I'm trying to create a VBA Code that on open of a spreadsheet it will write to an Access database. The purpose of this is to track how often our spreadsheets are used so we can eliminate non-used reports. I need all the help I can get. I am currently using Excel 2003 and Access 2000. I can post the code I swiped from the internet and tried manipulating to do what I need but I could never get it to write to my access db. Any help would be greatly appreciated!

Below is the code i've used:
'' ***************************************************************************
'' Purpose : Access Log File : Record process activity
'' Written : 25-Sep-2001 by Andy Wiggins - Byg Software Ltd
'' Notes : Needs a reference to DAO 3.5
''
Sub ALF(pStr_Cb As String, Optional pStr_Notes As String = "-")
Dim dbs As Database
Dim lStr_Sql As String
Dim numberOfRows
Dim lStr_DbName As String
Dim llng_Model_Id As Long
Dim wrkJet As Workspace
Dim SystemDBPath As String
Dim AccessEngine As DAO.DBEngine
Set AccessEngine = New DAO.DBEngine

SystemDBPath = "H:\AccessWrkGrp\master.mdw"
AccessEngine.SystemDB = SystemDBPath

On Error Resume Next
'Set wrkJet = New DAO.DBEngine
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Create a string holding a full name and path reference to the Access database
lStr_DbName = ThisWorkbook.Path & Application.PathSeparator & cStr_DbName

If 0 = Len(Dir(lStr_DbName)) Then Exit Sub

''Collects the current model's unique reference
llng_Model_Id = gStr_Model

''Test and, if necessary, amend the result
If Len(llng_Model_Id) = 0 Then llng_Model_Id = 0

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Prepare the query
''In the database, column 1 is an AutoNumber field
''So we only insert data into columns 2,3,4 and 5
lStr_Sql = ""
lStr_Sql = lStr_Sql & " INSERT INTO DataSource(Model_Id,Datex,Timex,Namex,Notesx)"
lStr_Sql = lStr_Sql & " VALUES(" & llng_Model_Id & ",#" & Format(Date, "dd-mmm-yyyy") & "#,#" & Time & "#,'" & pStr_Cb & "','" & pStr_Notes & "')"

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Workspace
' Set wrkJet = CreateWorkspace("", "Jon Graber", "colts01", dbUseODBC)

''Open the database
' Set dbs = wrkJet.OpenDatabase(lStr_DbName, False, False, "ODBC;UID=Jon Graber, PWD=colts01")

'SWIPED FROM THE WEB ***************************************8
Set wrkJet = AccessEngine.CreateWorkspace("", "Jon Graber", "colts01")

'Set dbs = AccessEngine.wrkJet.OpenDatabase(lStr_DbName)
Set dbs = AccessEngine.Workspaces(0).OpenDatabase(lStr_DbName)

'***********************************************************
With dbs
''Execute the query
.Execute lStr_Sql
''Close and..
.Close
End With

''..tidy up
Set dbs = Nothing

End Sub

 

[PHV]

I could never get it to write to my access db
What happens ? Any error message ? Computer crash ? ...
Comment out the On Error instruction and run your code in debug mode step by step.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ISUTri]

Thanks for the tip. The error is:
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.


Did I mention that we have security on our access databases where you have to login with a username and password to get to the data? If not how do i do this from excel?

 

[ISUTri]

I got Access to write to Excel. I had to set up the path to my workgroup file:

SystemDBPath = "\\path\master.mdw"
AccessEngine.SystemDB = SystemDBPath



Now my question is this: How can I grab the login name used to login to the LAN of the PC? I know this can be done in access VBA but what about Excel?

 

[PHV]

Environ("USERNAME") ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886