Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I write From Excel 2003 to Access 2000?
I'm trying to write some VBA Code so that when a User opens an Excel
Spreadsheet it logs it so that we know what reports are being used. I've found several examples of code but Ican't seem to get any of them to work (even talk to Access). So I'm not sure how to go about handling this. The Code I've been trying is as follows (I pulled this from another website): '' 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("", "USERNAME", "PASSWORD", dbUseODBC) ''Open the database ' Set dbs = wrkJet.OpenDatabase(DBName, False, False, "ODBC;UID=USERNAME, PWD=PASSWORD") '************************************************* ********** With dbs ''Execute the query .Execute lStr_Sql ''Close and.. .Close End With ''..tidy up Set dbs = Nothing End Sub Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Protection over Excel 2000 and 2003 | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Can I lock info in Excel 2003 and users in Excel 2000 enter info? | Excel Discussion (Misc queries) | |||
Problems using Excel 2000 to open/save file saved in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) |