Linking SQL Tbls w/form listbox

[Leftyleolady]

I am using code/form from "Access Programming 2000 for Dummies" to link table
from an SQL server to Access db. I have Doug Steele's and Joe Fallon's code
on this from previous answers to questions, which is a little beyond me
(conceptwise) at this time. I would like to understand the particulars
first. Okay, I am getting to the question...

I cannot get the below code to work... Can someone help me out. I get
either a Runtime 3421 data type conversion, or 3734 Database has been placed
in state, etc. I have referenced DAO 3.6 library and open database
exclusively, with no luck. I also went to the website and downloaded updates
to no avail.

Private Sub Command2_Click()
Dim Lnk As TableDef
Set Lnk = CurrentDb.CreateTableDef(Mid(Me.List0, 10))
Lnk.SourceTableName = Mid(Me.List0, 10)
Lnk.Connect = "ODBC;DSN=MSAToSQL;UID=ID;PWD=password"
CurrentDb.TableDefs.Append Lnk
DoCmd.OpenTable Mid(Me.List0, 10)
End Sub

Thanks in advance!

 

[Douglas J. Steele]

What is Me.List0 and what does it contain when you're trying to run the
code?

Try changing your routine to:

Private Sub Command2_Click()
Dim Lnk As TableDef
Dim strOutput As String

strOutput = "I'm going to use the following statements:" & vbCrLf & _
"CurrentDb.CreateTableDef(" & Mid(Me.List0, 10) & ")" & vbCrLf & _
"SourceTableName = " & Mid(Me.List0, 10) & vbCrLf & _
"Does that sound about right?"

If MsgBox(strOutput, vbYesNo) = vbYes Then
Set Lnk = CurrentDb.CreateTableDef(Mid(Me.List0, 10))
Lnk.SourceTableName = Mid(Me.List0, 10)
Lnk.Connect = "ODBC;DSN=MSAToSQL;UID=ID;PWD=password"
CurrentDb.TableDefs.Append Lnk
DoCmd.OpenTable Mid(Me.List0, 10)
End If
End Sub

What shows in the message box? Does it look right?

 

[Leftyleolady]

Hi Doug,

Thanks for taking a interest in my problem. Me.List0 is an unbound listbox
containing the tables from the server. It is filled by the following code:

Private Sub Form_Load()
Dim DBS As Database
Dim Tbl As TableDef
Dim Tablelist
Set DBS = OpenDatabase("MSAtoSQL", _
dbDriverNoPrompt, False, _
"ODBC;UID=ID;PWD=password")
For Each Tbl In DBS.TableDefs
Tablelist = Tablelist & Tbl.Name & ";"
Next
With Me.List0
.RowSourceType = "Value List"
.RowSource = Tablelist
End With
End Sub

When I copied and pasted your alterations, I get ...."SourceTableName =
". When I click yes, I get the 3421 Data Type conversion error.

Thanks again,

 

[Douglas J Steele]

Sorry, are you saying that message box didn't have a name for the table to
be created?

 

[Leftyleolady]

Yes, that is correct. It is blank, nothing after the equals sign. I thought
maybe it need a reference to DAO 3.51, didn't work.

 

[Douglas J Steele]

Well, not having data would certainly explain why your code isn't working!
<g>

If your listbox contains the names of the tables in the back-end database,
why are you removing the first 9 characters from each name? Mid(Me.List0,
10)

 

[Leftyleolady]

I know, I don't get it?? The form listbox is populated with all the server
tables. The first 9 characters are is the server prefix (like dbo_).

So what could it be?

 

[Douglas J Steele]

Hold on. You're using a list box. What is its MultiSelect property set to?
It must be None in order to refer to the list box like that and get a value
back.

 

[Leftyleolady]

Yes, the MultiSelect property is set to NONE. Could that be the issue?

 

[Douglas J. Steele]

No, as I said, it has to be set to None in order to work. I was really
hoping it wasn't! <g>

 

[Leftyleolady]

Well, bummer. The website to download the Access database (FormsandData.mdb)
with form and Code is at www.wbase2.com, if you are still interseted in
solving this mystery. (I hope you are.)

 

[Douglas J Steele]

Since I won't be able to connect to the external database to get the list of
tables, that won't be of much use to me.

 

[SteveS]

Looked at www.wbase2.com. Can't find the mdb or a link to download it. Also
getting a lot of 404 and 500 errors.

What page do I goto to download the FormsandData.mdb?

 

[Leftyleolady]

Go to Rob's for Dummies Books and click. The db is in Item #6. Yes, the
website is giving those errors for the ADO links I have sent an email to
author regarding.

Any help you can give Steve would be great!

 

[SteveS]

I looked at the mdb. I don't have access to SQL server so I attached to
Northwind.mdb on my G:\ drive.

The form_load() code fills the list box; I have to assume that the list box
gets filled when it attachs to SQL server.

I would do the trimming of the table names in the Form_Load() code:

Private Sub Form_Load()
Dim DBS As Database
Dim Tbl As TableDef
Dim Tablelist As String

Set DBS = OpenDatabase("MSAtoSQL", _
dbDriverNoPrompt, False, _
"ODBC;UID=apd;PWD=dummies")
For Each Tbl In DBS.TableDefs
Tablelist = Tablelist & Mid(Tbl.Name, 10) & ";"
Next
With Me.List0
.RowSourceType = "Value List"
.RowSource = Tablelist
End With

DBS.Close
Set DBS = Nothing
End Sub


Now the list box should contain a list of the tables.

Run this code from the command button:

Private Sub Command2_Click()
Dim Lnk As TableDef
Dim strList As String

strList = Me.List0
MsgBox "Table name selected from the list box is: " & strList

'Set Lnk = CurrentDb.CreateTableDef(strList)
'Lnk.SourceTableName = strList
'Lnk.Connect = "ODBC;DSN=MSAToSQL;UID=apd;PWD=dummies"
'CurrentDb.TableDefs.Append Lnk
'DoCmd.OpenTable strList
End Sub


If the message box returns the correct table name, then uncomment the last 5 lines.

Set a breakpoint at the line "Set Lnk = ..." and step thru to see if/where an
error occurs.

If you can't get a value from Me.List0 in the Command2_Click() code, you
obviously can't link/open the table.