problem with linking to excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having some problems with one of my Access databases and you all seem
like a very knowledgeable group so I will present my problem.

I link to an excel spreadsheet (using TransferSpreadsheet in VBA) and then
run an update query on that linked spreadsheet. The problem is that this
corrupts the data in the spreadsheet! The SQL statement I run is :

UPDATE XLSheet INNER JOIN Table1 ON XLSheet.FNo=Table1.ActiveFNo
SET XLSheet.[New Supplier Comments] = Table1.note;

Does anyone know why this would fail? If I Import the spreadsheet rather
than link to it, it works fine! But this isn't a good solution for me
because I need to change the data in the spreadsheet from Access in VBA code.

Has anyone done something like this? It would be a big confidence booster
for someone to say that it has worked for them. I have a bad feeling that
this is a bug in Access and that I am stuck

I'm using Access 2002 SP3.

Thanks in advance,
Nate
 
You cannot "link" to a spreadsheet via TransferSpreadsheet; that imports or
exports only. Post all the code that you're using to "connect" to the
spreadsheet.
 
Actually, you can. Using acLink instead of acImport or acExport. But I have a
feeling that I'm taking the the capabilities of Access VBA a bit too far when
I try to do update SQL queries on that linked spreadsheet. Here is my
connection code. I first open up the spreadsheet and add some apostrophes so
that it will link some numbers as text. Then I link it up.

Set Ex = New Excel.Application
Ex.Visible = True
Set Wb = Ex.Workbooks.Open(S, False)
On Error GoTo Failed

' Setup the table for all entries
Set Ws = Wb.Worksheets("All Faults")

'some code removed that just adds some apostrophes

Wb.Names.Add "Data", "='All Faults'!$A$8:$P$" & Trim(Str(I - 1))
Wb.Save

If S <> "" Then
On Error GoTo Failed
If Not IsNull(Access.CurrentDb.TableDefs("GT")) Then
Access.CurrentDb.TableDefs.Delete ("GT")

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "GT",
S, True, "Data"
'On Error GoTo 0
End If


Thanks for looking at it.
Nate

Ken Snell said:
You cannot "link" to a spreadsheet via TransferSpreadsheet; that imports or
exports only. Post all the code that you're using to "connect" to the
spreadsheet.

--

Ken Snell
<MS ACCESS MVP>

Nate R said:
I'm having some problems with one of my Access databases and you all seem
like a very knowledgeable group so I will present my problem.

I link to an excel spreadsheet (using TransferSpreadsheet in VBA) and then
run an update query on that linked spreadsheet. The problem is that this
corrupts the data in the spreadsheet! The SQL statement I run is :

UPDATE XLSheet INNER JOIN Table1 ON XLSheet.FNo=Table1.ActiveFNo
SET XLSheet.[New Supplier Comments] = Table1.note;

Does anyone know why this would fail? If I Import the spreadsheet rather
than link to it, it works fine! But this isn't a good solution for me
because I need to change the data in the spreadsheet from Access in VBA
code.

Has anyone done something like this? It would be a big confidence booster
for someone to say that it has worked for them. I have a bad feeling that
this is a bug in Access and that I am stuck

I'm using Access 2002 SP3.

Thanks in advance,
Nate
Click to expand...
Click to expand...
 
Back
Top