InstructionWhich7142
Registered User.
- Local time
- Today, 11:42
- Joined
- Feb 24, 2010
- Messages
- 199
ODBC Without "select unique record identifier", FoxPro DSN-Less, Easy Late Binding
So I've been looking into the following:
After reading MANY very long code samples on all 3 and with a couple of days work I had some quite simple success and also learnt a few facts, as I had such a nightmare finding this info on Google (most threads have the right question and vague solutions) I thought I should share.
Feel free to delete this/move/edit if you think it would be better else where or isn't really very helpful
**********************************************
**********************************************
1. "DSN-Less Connection" The simple answer to this is "It doesn't work with FoxPro"
This form Microsoft explains some of it:
http://support.microsoft.com/kb/212886/EN-US
I assume it would work for SQL etc but there is a definite bug with the FoxPro drivers and a system DSN must be created (you can test this by making a FileDSN in your documents folder and trying to connect, you'll get the same Reserved Error -7778 from Windows, you are not doing something wrong in VBA!
**********************************************
**********************************************
2. "ODBC Link without "select unique record identifier" - To do this read only (And considering the above FoxPro limitation) and the fun you can have with ODBC connection strings, if you have a System DSN already but find "select unique record identifier" annoying when doing
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=mysource", acTable, "sourcetable", "destinationtable"
you can just do this:
basically make your own function called OdbcLink("xxx") and call it when ever you want to link tables as read only.
Its as simple as that (and maybe simpler but that's as far as I got!)
If you want to be able to write back then you just need to change the OpenDatabase(SystemDsnName, False, True, "ODBC") so its not read only, then you can use the SQL "Alter Table" to add one (I've not tried this) Samples online look like:
ALTER TABLE dbo_EC_Employee ADD CONSTRAINT NaturalKey UNIQUE(Person_ID, Company_ID)
So read along those lines
**********************************************
**********************************************
3. Late Binding:
This link helped a lot, it explains (And shows very clearly) the differences in how you start to work with another program:
http://rogersaccessblog.blogspot.co.uk/2010/06/whats-difference-between-early-binding.html
I went for Late binding without Intellisense (the 4th sample) (autocomplete for expressions)
As this is basically what my code looked like already,
But I got errors like Run-Time error '1004': Application-Defined or Object-defined error
or "Variable not defined" with Option Explicit on
Where I got stuck (and where the best tip I happened to read at the bottom of a long post) is that all the Object names, EG:
xlSheet.Cells.Borders(xlEdgeLeft)
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
which aren't recognised by Access as the Library isn't linked, need to be replaced by raw Values:
xlSheet.Cells.Borders(7)
.Underline = -4142
.ColorIndex = -4105
.TintAndShade = 0
.ThemeFont = 2
These can be looked up within Excel's VBA help, or make an Excel VBA Sub:
Msgbox xlUnderlineStyleNone
will give you the corresponding value on Pop up of:
-4142
the Minus is important
I hope someone finds this helpful for one of these issues, I think I managed to Name Drop everything I Googled, if anyone can add anything else just edit it in at the bottom here
So I've been looking into the following:
- Trying to Link to tables through ODBC using "DSN-Less" methods (without having to add a system DSN) gives
Code:
Run-Time Error '3000': Reserved error (-7778); There is no message for this error - Linking tables through ODBC (to use as Read Only for reporting) without the annoying "select unique record identifier"/"select a unique record identifier" Message
- Also changing Access VBA code that formats exported spreadsheets so it will work without a Reference to the Excel Library, basically Late Binding
After reading MANY very long code samples on all 3 and with a couple of days work I had some quite simple success and also learnt a few facts, as I had such a nightmare finding this info on Google (most threads have the right question and vague solutions) I thought I should share.
Feel free to delete this/move/edit if you think it would be better else where or isn't really very helpful
**********************************************
**********************************************
1. "DSN-Less Connection" The simple answer to this is "It doesn't work with FoxPro"
This form Microsoft explains some of it:
http://support.microsoft.com/kb/212886/EN-US
I assume it would work for SQL etc but there is a definite bug with the FoxPro drivers and a system DSN must be created (you can test this by making a FileDSN in your documents folder and trying to connect, you'll get the same Reserved Error -7778 from Windows, you are not doing something wrong in VBA!
**********************************************
**********************************************
2. "ODBC Link without "select unique record identifier" - To do this read only (And considering the above FoxPro limitation) and the fun you can have with ODBC connection strings, if you have a System DSN already but find "select unique record identifier" annoying when doing
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=mysource", acTable, "sourcetable", "destinationtable"
you can just do this:
basically make your own function called OdbcLink("xxx") and call it when ever you want to link tables as read only.
Code:
Sub IwantToLinkThisTable()
OdbcLink ("NameOfMyTable")
End Sub
_________________________________________________________________
Public Function OdbcLink(TableNameToLink As String)
Dim OdbcDatabase As Database
Dim TableToLinkDef As TableDef
Dim SystemDsnName As String
SystemDsnName = "NameOfMySystemDSN"
Set OdbcDatabase = OpenDatabase(SystemDsnName, False, True, "ODBC")
Set TableToLinkDef = CurrentDb.CreateTableDef(TableNameToLink, 0, TableNameToLink)
TableToLinkDef.Connect = OdbcDatabase.Connect
CurrentDb.TableDefs.Append TableToLinkDef
End FunctionIts as simple as that (and maybe simpler but that's as far as I got!)
If you want to be able to write back then you just need to change the OpenDatabase(SystemDsnName, False, True, "ODBC") so its not read only, then you can use the SQL "Alter Table" to add one (I've not tried this) Samples online look like:
ALTER TABLE dbo_EC_Employee ADD CONSTRAINT NaturalKey UNIQUE(Person_ID, Company_ID)
So read along those lines
**********************************************
**********************************************
3. Late Binding:
This link helped a lot, it explains (And shows very clearly) the differences in how you start to work with another program:
http://rogersaccessblog.blogspot.co.uk/2010/06/whats-difference-between-early-binding.html
I went for Late binding without Intellisense (the 4th sample) (autocomplete for expressions)
As this is basically what my code looked like already,
But I got errors like Run-Time error '1004': Application-Defined or Object-defined error
or "Variable not defined" with Option Explicit on
Where I got stuck (and where the best tip I happened to read at the bottom of a long post) is that all the Object names, EG:
xlSheet.Cells.Borders(xlEdgeLeft)
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
which aren't recognised by Access as the Library isn't linked, need to be replaced by raw Values:
xlSheet.Cells.Borders(7)
.Underline = -4142
.ColorIndex = -4105
.TintAndShade = 0
.ThemeFont = 2
These can be looked up within Excel's VBA help, or make an Excel VBA Sub:
Msgbox xlUnderlineStyleNone
will give you the corresponding value on Pop up of:
-4142
the Minus is important
I hope someone finds this helpful for one of these issues, I think I managed to Name Drop everything I Googled, if anyone can add anything else just edit it in at the bottom here
