transferspreadsheet problems

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

Guest

Hi:

I'm using the following code to transfer a spreadsheet into the database and
display the fields.

It generally works OK, but the problems I have are:
1. I can't edit the database after loading a spreadsheet as Access says that
another user has the DB open.
2. Sometimes Access can't find the spreadsheet when it's transfered (Can't
find object), even though it's visible in the DB Window. I have that issue in
another part of the database too when I copy the transfered table, when I try
to open it as a recordset Access complains that it can't find it??

Thanks,
Perry

On Error Resume Next
DoCmd.DeleteObject acTable, "Spreadsheet"
DoCmd.DeleteObject acTable, "DuplicateSpreadsheet"
Application.RefreshDatabaseWindow

On Error GoTo Err_btnLoadSpreadsheet_Click
SpreadsheetFileName = GetOpenFile(, "Import a Spreadsheet")

If SpreadsheetFileName = "" Then Exit Sub
Dim lngAdded As Long, strSQL As String

On Error GoTo Err_btnLoadSpreadsheet_Click
DoCmd.TransferSpreadsheet acImport, , "Spreadsheet",
SpreadsheetFileName, True

Application.RefreshDatabaseWindow

Dim tblSpreadsheet As Recordset, fld As Field, dbs As Database
Set dbs = CurrentDb
Set tblSpreadsheet = dbs.OpenRecordset("Spreadsheet")
For x = 0 To lstFields.ListCount - 1
lstFields.RemoveItem (0)
Next

For Each fld In tblSpreadsheet.Fields
Me.lstFields.AddItem fld.Name
Next

Call ShowFileButtons(False)
lstFields.Visible = True
[Available Fields_Label].Visible = True
Me.btnReload.Visible = True
Me.lblLoadedSpreadsheet.Caption = Mid(SpreadsheetFileName,
InStrRev(SpreadsheetFileName, "\") + 1)
dbs.Close
Set dbs = Nothing
 
PerryM said:
Hi:

I'm using the following code to transfer a spreadsheet into the
database and display the fields.

It generally works OK, but the problems I have are:
1. I can't edit the database after loading a spreadsheet as Access
says that another user has the DB open.
2. Sometimes Access can't find the spreadsheet when it's transfered
(Can't find object), even though it's visible in the DB Window. I
have that issue in another part of the database too when I copy the
transfered table, when I try to open it as a recordset Access
complains that it can't find it??

Thanks,
Perry

On Error Resume Next
DoCmd.DeleteObject acTable, "Spreadsheet"
DoCmd.DeleteObject acTable, "DuplicateSpreadsheet"
Application.RefreshDatabaseWindow

On Error GoTo Err_btnLoadSpreadsheet_Click
SpreadsheetFileName = GetOpenFile(, "Import a Spreadsheet")

If SpreadsheetFileName = "" Then Exit Sub
Dim lngAdded As Long, strSQL As String

On Error GoTo Err_btnLoadSpreadsheet_Click
DoCmd.TransferSpreadsheet acImport, , "Spreadsheet",
SpreadsheetFileName, True

Application.RefreshDatabaseWindow

Dim tblSpreadsheet As Recordset, fld As Field, dbs As Database
Set dbs = CurrentDb
Set tblSpreadsheet = dbs.OpenRecordset("Spreadsheet")
For x = 0 To lstFields.ListCount - 1
lstFields.RemoveItem (0)
Next

For Each fld In tblSpreadsheet.Fields
Me.lstFields.AddItem fld.Name
Next

Call ShowFileButtons(False)
lstFields.Visible = True
[Available Fields_Label].Visible = True
Me.btnReload.Visible = True
Me.lblLoadedSpreadsheet.Caption = Mid(SpreadsheetFileName,
InStrRev(SpreadsheetFileName, "\") + 1)
dbs.Close
Set dbs = Nothing
Click to expand...

One thing that pops out immediately at me is that you don't close the
recordset you opened, nor set it to Nothing. I don't quite see how that
would necessarily give you both of the problems you're having, but it
could plausibly be responsible for the first one. Technically, you
shouldn't close the dbs object, since you didn't open it, but rather got
it from CurrentDb(); however, in my experience it doesn't actually do
any harm to close it.
 
Hi:

tried closing the recordset and not closing the database with no difference.

With not finding database objects, it's like the database is needs
refreshing??

Perry


Dirk Goldgar said:
PerryM said:
Hi:

I'm using the following code to transfer a spreadsheet into the
database and display the fields.

It generally works OK, but the problems I have are:
1. I can't edit the database after loading a spreadsheet as Access
says that another user has the DB open.
2. Sometimes Access can't find the spreadsheet when it's transfered
(Can't find object), even though it's visible in the DB Window. I
have that issue in another part of the database too when I copy the
transfered table, when I try to open it as a recordset Access
complains that it can't find it??

Thanks,
Perry

On Error Resume Next
DoCmd.DeleteObject acTable, "Spreadsheet"
DoCmd.DeleteObject acTable, "DuplicateSpreadsheet"
Application.RefreshDatabaseWindow

On Error GoTo Err_btnLoadSpreadsheet_Click
SpreadsheetFileName = GetOpenFile(, "Import a Spreadsheet")

If SpreadsheetFileName = "" Then Exit Sub
Dim lngAdded As Long, strSQL As String

On Error GoTo Err_btnLoadSpreadsheet_Click
DoCmd.TransferSpreadsheet acImport, , "Spreadsheet",
SpreadsheetFileName, True

Application.RefreshDatabaseWindow

Dim tblSpreadsheet As Recordset, fld As Field, dbs As Database
Set dbs = CurrentDb
Set tblSpreadsheet = dbs.OpenRecordset("Spreadsheet")
For x = 0 To lstFields.ListCount - 1
lstFields.RemoveItem (0)
Next

For Each fld In tblSpreadsheet.Fields
Me.lstFields.AddItem fld.Name
Next

Call ShowFileButtons(False)
lstFields.Visible = True
[Available Fields_Label].Visible = True
Me.btnReload.Visible = True
Me.lblLoadedSpreadsheet.Caption = Mid(SpreadsheetFileName,
InStrRev(SpreadsheetFileName, "\") + 1)
dbs.Close
Set dbs = Nothing
Click to expand...

One thing that pops out immediately at me is that you don't close the
recordset you opened, nor set it to Nothing. I don't quite see how that
would necessarily give you both of the problems you're having, but it
could plausibly be responsible for the first one. Technically, you
shouldn't close the dbs object, since you didn't open it, but rather got
it from CurrentDb(); however, in my experience it doesn't actually do
any harm to close it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
BTW: any idea what UseOA in the TransferSpreadsheet function is?

Dirk Goldgar said:
PerryM said:
Hi:

I'm using the following code to transfer a spreadsheet into the
database and display the fields.

It generally works OK, but the problems I have are:
1. I can't edit the database after loading a spreadsheet as Access
says that another user has the DB open.
2. Sometimes Access can't find the spreadsheet when it's transfered
(Can't find object), even though it's visible in the DB Window. I
have that issue in another part of the database too when I copy the
transfered table, when I try to open it as a recordset Access
complains that it can't find it??

Thanks,
Perry

On Error Resume Next
DoCmd.DeleteObject acTable, "Spreadsheet"
DoCmd.DeleteObject acTable, "DuplicateSpreadsheet"
Application.RefreshDatabaseWindow

On Error GoTo Err_btnLoadSpreadsheet_Click
SpreadsheetFileName = GetOpenFile(, "Import a Spreadsheet")

If SpreadsheetFileName = "" Then Exit Sub
Dim lngAdded As Long, strSQL As String

On Error GoTo Err_btnLoadSpreadsheet_Click
DoCmd.TransferSpreadsheet acImport, , "Spreadsheet",
SpreadsheetFileName, True

Application.RefreshDatabaseWindow

Dim tblSpreadsheet As Recordset, fld As Field, dbs As Database
Set dbs = CurrentDb
Set tblSpreadsheet = dbs.OpenRecordset("Spreadsheet")
For x = 0 To lstFields.ListCount - 1
lstFields.RemoveItem (0)
Next

For Each fld In tblSpreadsheet.Fields
Me.lstFields.AddItem fld.Name
Next

Call ShowFileButtons(False)
lstFields.Visible = True
[Available Fields_Label].Visible = True
Me.btnReload.Visible = True
Me.lblLoadedSpreadsheet.Caption = Mid(SpreadsheetFileName,
InStrRev(SpreadsheetFileName, "\") + 1)
dbs.Close
Set dbs = Nothing
Click to expand...

One thing that pops out immediately at me is that you don't close the
recordset you opened, nor set it to Nothing. I don't quite see how that
would necessarily give you both of the problems you're having, but it
could plausibly be responsible for the first one. Technically, you
shouldn't close the dbs object, since you didn't open it, but rather got
it from CurrentDb(); however, in my experience it doesn't actually do
any harm to close it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
PerryM said:
Hi:

tried closing the recordset and not closing the database with no
difference.
Click to expand...

Puzzling. Is there more code in the procedure after the part you
posted? What's in the routine ShowFileButtons?
With not finding database objects, it's like the database is needs
refreshing??
Click to expand...

I don't see why, though. But try adding a DoEvents statement before the
"Set dbs = CurrentDb" line, just to make sure that Access has finished
doing all your deleting and linking.
 
PerryM said:
BTW: any idea what UseOA in the TransferSpreadsheet function is?
Click to expand...

It's not supported any more, if it ever was. As I recall, it's supposed
to stand for "Use Office Automation", but I don't know exactly what it
was supposed to do.
 
Hi and thanks:

No other code apart from error handler. The ShowFileButtons procedure just
sets a series of buttons .visible as true or false.

Added DoEvents: didn't help.

I also commented out everything except the TransferSpreadsheet code and the
dbs = CurrentDB: There's the problem. Don't have the dbs assignment and it
works fine, or don't TransferSpreadsheet and it works fine (except it doesn't
do what it's meant to).

The dbs assignment opens the database twice which means that edits can't be
made because it's not opened exclusively: but this is the only time that's
happened??

Odd. The Database works fine, just doesn't like it when I want to edit
anything!

Perry
 
PerryM said:
Hi and thanks:

No other code apart from error handler. The ShowFileButtons procedure
just sets a series of buttons .visible as true or false.

Added DoEvents: didn't help.

I also commented out everything except the TransferSpreadsheet code
and the dbs = CurrentDB: There's the problem. Don't have the dbs
assignment and it works fine, or don't TransferSpreadsheet and it
works fine (except it doesn't do what it's meant to).

The dbs assignment opens the database twice which means that edits
can't be made because it's not opened exclusively: but this is the
only time that's happened??

Odd. The Database works fine, just doesn't like it when I want to edit
anything!
Click to expand...

Sorry, I overlooked that this message thread was still unresolved. I'm
at a loss to say what's wrong here without looking at your database in
more detail. If you'd like to send me a cut-down copy of your database,
containing only the elements necessary to demonstrate the problem,
compacted and then zipped to less than 1MB in size (preferably much
smaller) -- I'll have a look at it, time permitting. You can send it
to the address derived by removing NO SPAM from the reply address of
this message.
 
Back
Top