MDE and Late Binding

[LeAnn]

Hello,

I have Access 2003. I was trying to create an mde but was getting an error
"....unable to create mde". I followed Allen Brownes steps of decompiling
etc and still received the error. Evidently it has to do with my references
and now I'm confused.

I use late binding for opening an Excel spreadsheet (Dim xlBook as Object
for example) and while developing I have the Excel reference turned on. From
what I have read and to avoid version incompatibility, late binding should be
used and the Excel reference should be removed from the VBA window. When I
did that, the mde could not be created. When I put it back, the mde was
created. Can someone help me understand why this is the case - have I
misunderstood late binding? Can this not be used when splitting a database
and creating an mde?

Thanks
LeAnn

 

[LeAnn]

Also, I just realized that in my FE.mdb I have created links to my BE.mdb
tables. When I created the mde, Access dropped 1 of my table links and made
it a local table. Any reason why it would do that? This table and a couple
of others are created though Make-Table queries to get copies of data from
our other data sources. Should those just be local to the front end anyway?

 

[LeAnn]

Never mind on this question - I realize now what was happening. And the
answer to my question here is - yes.

I'd still like some clarification on my previous post. Thanks.

 

[Tony Toews [MVP]]

I have Access 2003. I was trying to create an mde but was getting an error
"....unable to create mde". I followed Allen Brownes steps of decompiling
etc and still received the error. Evidently it has to do with my references
and now I'm confused.

I use late binding for opening an Excel spreadsheet (Dim xlBook as Object
for example) and while developing I have the Excel reference turned on. From
what I have read and to avoid version incompatibility, late binding should be
used and the Excel reference should be removed from the VBA window. When I
did that, the mde could not be created. When I put it back, the mde was
created. Can someone help me understand why this is the case - have I
misunderstood late binding? Can this not be used when splitting a database
and creating an mde?

Sounds like some of the code hasn't completely been converted to use
late binding. What happens when you remove the Excel reference and
compile the project? What error messages do you get?

If necessary please post the relevant lines of code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[LeAnn]

Hi Tony,

I believe you are right. Your response made me do a search for "Excel" and
found this which is likely the offending code:
Dim xlApp As Object
Dim xlBook As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("T:\3.0\Excel template")
xlApp.Application.Visible = True

Most are coded like the example below which is used during a loop through a
directory to import all files present:
Dim xlBook As Object
Set xlBook = GetObject(UDPDIR & strFile)
Print #lngLFile, vbNewLine & "FILE IMPORT: " & strFile

How should I revise the first snippet to be late binding. I tried but didn't
open the template:

Dim xlBook As Object
Set xlBook = GetObject("T\3.0\Excel Template")

Thanks

 

[LeAnn]

Oops I found another offending piece of code. This is opening a text file in
Excel looping to skip unneeded rows then copies the remaining rows into an
Excel template for further processing. Any suggestions on changing this?

Set objXL = CreateObject("Excel.Application")
objXL.DisplayAlerts = False

objXL.Workbooks.OpenText FileName:=strSNDir & strFile, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
2), _
TrailingMinusNumbers:=True

more stuff then.....

strCell = "A" & lngRCnt & ":A281"
Set objRange = objSheet.Range(strCell)
objRange.Select
objRange.Copy

 

[Tony Toews [MVP]]

I believe you are right. Your response made me do a search for "Excel" and
found this which is likely the offending code:

The code looks reasonable when I did a quick look at it and compared
it to http://www.granite.ab.ca/access/latebinding.htm.

However what happened when you removed the Excel reference and
compiled the project? That should find the offending code rapidly.
For example there may be an Excel specific constant being used
somewhere.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[LeAnn]

When I do a debug and compile without the reference the line of code below
(xlDelimited specifically) is the problem. If I remove the reference after
compiling and try to make the mde, I get "Access could not create the mde."
I guess I can just keep the reference and make sure the PCs have Office 2003.

objXL.Workbooks.OpenText FileName:=strSNDir & strFile, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1,
2), _
TrailingMinusNumbers:=True

 

[Douglas J. Steele]

If you remove the reference to the Excel library, you need either to replace
xlDelimited and xlDoubleQuote with their values (they're both equal to 1),
or else declare them as constants in your code:

Const xlDelimited As Long = 1
Const xlDoubleQuote As Long = 1

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[LeAnn]

Oh, I seem to remember reading that somewhere - thanks Doug worked like a
charm.

I decided to use shell for my other procedure which opens an Excel Template.
The following code works but it "opens" the template rather than creating a
new document based on the template. What should I do?

Dim OpenExcel As Variant
Dim ExcelFile As String
ExcelFile = "T:\my path\Template 3.xlt"
OpenExcel = Shell("Excel.exe " & """" & ExcelFile & """", 3)

 

[Douglas J. Steele]

Sorry, I don't work with Excel templates, so I don't know the answer.

 

[LeAnn]

Thanks for your help Doug. I got an idea how to deal with the template from
a post by strive4peace.

 

[David W. Fenton]

If you remove the reference to the Excel library, you need either
to replace xlDelimited and xlDoubleQuote with their values
(they're both equal to 1), or else declare them as constants in
your code:

Const xlDelimited As Long = 1
Const xlDoubleQuote As Long = 1

What happens if you declare the constants in your own code and then
revert to early binding? Is there a namespace collision then? If
there's no namespace collision, which declaration wins? Your local
one or the external one?

I think I'd use a different constant name if I were doing this, just
to insure that I could easily revert to early binding without
problems.