Replies

Just to let you know that I finally solved this problem by starting the PC in Safe Mode. This then allowed me to copy the file to another folder & everything worked again.

Thanks for all the helpful suggestions

Nick

I tried that but with exactly the same result – I still cannot open it.

Paul

Thanks for the reply. I have checked the Security tab & everyone has Full Control of the file.

When I try to do anything to it however, I get the message “You require permission from NICKBODD-PCNick to make changes to this file.” I am logged on as Nick and my Pc is called NICKBODD-PC. The only possible problem I can see is that if I go through Control Panel/System the PC is named “nickbodd-PC”, and I cannot change that – the OK button is greyed out. Might that be significant ?

Nick

Apologies – I did not realise the sites were linked.

Unfortunately no replies on either thread.

Wendell

Thanks for your reply.

I am using Office 2003 SP3 for Access and Excel. The code occurs in a class module which I am building to provide a generic function to import an Excel worksheet, or a range on a worksheet, into an Access database. TransferSpreadsheet is too limiting for this, eg it overwrites existing data in the table whether you want it to or not.

I had not thought of temporarily linking to the spreadsheet, but I will give this a try. I cannot guarantee whether any cells have >255 chars, nor in which row they would appear if they did.

Bodders

I got ahead of myself when I said that using the ODBC driver actually solved the problem.

Instead of just ignoring the invalid rows where a cell had >255 characters, it now returns this error on the rstXLS.MoveNext statement :

[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

What I really wanted to be able to do was to have some code that would check the field length, and then only move the first 255 characters into the database field – but the code will never reach this point as it will error on the MoveNext statement.

Any more thoughts on this ?

Bodders

Wendell & Reid – thank you both for your replies.

The answer was to use ODBC instead of OLEDB, as below :

‘
‘———————————————————–
‘This is the OLE DB driver
‘———————————————————–
‘With cnnXLS
‘ .Provider = “Microsoft.Jet.OLEDB.4.0”
‘ strConn = “Data Source=” & Me.XLSName & “;Extended Properties=””Excel 8.0;HDR=YES;IMEX=1″””
‘ .ConnectionString = strConn
‘ .Open
‘End With
‘
‘———————————————————–
‘This is the ODBC driver
‘———————————————————–
With cnnXLS
.Provider = “MSDASQL”
strConn = “Data Source=” & Me.XLSName & “;Extended Properties=””Excel 8.0;HDR=YES;IMEX=1″””
.ConnectionString = “Driver={Microsoft Excel Driver (*.xls)};” & _
“DBQ=” & Me.XLSName & “; ReadOnly=False;”
.Open
End With

Saved me from a lot of heartache

Thanks again guys

Bodders

You could use code like this:

Code:

  Dim intValue As Integer
  Dim i As Integer
  intValue = 1 ' the ID of the record to select
  For i = 0 To Me.ComboBox1.ListCount - 1
	If Me.ComboBox1.List(i) = intValue Then
	  Me.ComboBox1.ListIndex = i
	  Exit For
	End If
  Next i

where ComboBox1 is the name of the combo box.

Hans

Thanks very much for that. I was hoping there was just a property like Itemdata in an Access form to set, but it looks like I will have to use this loop.

Bodders

Hans

Thanks for that – I will give it a try.

Bodders

Brilliant Hans – that is exactly what I needed. Took about 5 mins to run.

Many thanks

Bodders

Hans

Thanks for checking. I have managed to find a way around this – I closed the form & re-opened it hidden in Design view. I then set the Height and Top properties of the subforms, saved the form, and finally re-opened it in Normal view. No scrollbars in sight.

Nick

Hans

I have a main form which has 9 subforms. I am dynamically resizing them according to how many records they contain. This process is run when a command button is clicked.

It all works well apart from some subforms display scrollbars, seemingly at random. When I click the command button again, the subforms display correctly, i.e. no scrollbars.

Do you have any suggestions as to why this should be ?

Thanks for your help

Nick

Andy

This is not specific to reports but I have found http://www.joelonsoftware.com/%5B/url%5D to be a really useful resource. There’s lots of articles and discussion boards which I am sure would be helpful.

Regards

Nick

Hans

It does. The first 5 columns are text, and are fixed. The next 5 are the months from the current one to month+4. These will change every month.

Regards

Nick

Hans

I specified the column headings as a string as follows (as they appear when the query runs):

“Voyage”,”load Port”,”Discharge Port”,”Expo Start”,”Expo End”,01/03/2008″,”01/04/2008″,”01/05/2008″,”01/06/2008″,”01/07/2008″

I really need the form as a subform as it fits in with several other subforms on the main form

Regards

Nick