PC Review


Reply
Thread Tools Rate Thread

Access database error

 
 
Fredrik WG
Guest
Posts: n/a
 
      14th Jan 2008
I have sporadically found an error when accessing an access database from
Excel VBA, sometimes I get double entries (lines) in Excel when there is only
one entriy in Access database. The double entriy I see are just a copy of an
existing line.

Database are located in house on network drive.

Question: Does anyone know the cause of this error / behaviour? The solution
today is to verify that no double entries exists, and if it does, promt
operator to re-run macro until it pass.

VBA Code:
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & FileToOpen & ";FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT BOAT.ShortID, BOAT.Rev, BOAT.TestGroup, BOAT.MPPoint,
BOAT.MPDescription, BOAT.LtLow, BOAT.LtHigh, BOAT.Meas, BOAT.Unit,
BOAT.Cause, " _
, _
"BOAT.CompName, BOAT.Action, BOAT.Comments, BOAT.TSResp, BOAT.Pos,
BOAT.CompNumb, BOAT.RepairHistory, BOAT.RepCnt, BOAT.Status, BOAT.DateUpdate,
" _
, _
"BOAT.IR, BOAT.DateStamp, BOAT.ID, BOAT.IDNumber, BOAT.MPGroup,
BOAT.CustomerCom, BOAT.Loc FROM BOAT ORDER BY BOAT.IDNumber, BOAT.Loc")

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access error 3197 and JETCOMP.exe error "Error compacting database =?Utf-8?B?Vmlja3k=?= Microsoft Access 2 21st Jul 2007 12:29 AM
database access error gpl666666@hotmail.com Microsoft Frontpage 1 20th Feb 2007 11:23 PM
Excel query of Access database - changed database filename, now error ucdcrush@gmail.com Microsoft Excel Discussion 2 8th Mar 2006 08:42 PM
Error 3044-Access 2003 Database Error =?Utf-8?B?YnJlbmRhbmhlY2ttYW4=?= Microsoft Access Getting Started 1 30th Nov 2005 02:59 PM
Error when converting a access 2003 database for the access 2000 d =?Utf-8?B?amxlYWw=?= Microsoft Access 0 21st Oct 2005 10:51 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:18 PM.