Importing Tales and Columns

[BBAL20]

I'm trying to import certain columns within tables via ODBC currently stored
in a SQL enviroment. I'm a beginner when it comes to VB so please forgive me.
I've wrriten the following code to import the table but I only want certain
columns within that table, not the whole table. Suggestions?

Option Compare Database

Private Sub BASE_Click()

Dim SQL_Text As String

DoCmd.TransferDatabase acImport, "ODBC Database", _

"ODBC;DSN=ODS_FDR;APP=Microsoft Office
2003;WSID=PAS-KPATTERS;DATABASE=ODS_FDR;Trusted_Connection=YES;ArrayFetchOn=1;ArrayBufferSize=8", _

acTable , "dbo.BASE", "dbo_BASE" & Format(Date, "mmddyy")

MsgBox "Data transfer to Access is complete.", vbOKOnly, "Status"

End Sub

 

[John W. Vinson]

I'm trying to import certain columns within tables via ODBC currently stored
in a SQL enviroment. I'm a beginner when it comes to VB so please forgive me.
I've wrriten the following code to import the table but I only want certain
columns within that table, not the whole table. Suggestions?

Option Compare Database

Private Sub BASE_Click()

Dim SQL_Text As String

DoCmd.TransferDatabase acImport, "ODBC Database", _

"ODBC;DSN=ODS_FDR;APP=Microsoft Office
2003;WSID=PAS-KPATTERS;DATABASE=ODS_FDR;Trusted_Connection=YES;ArrayFetchOn=1;ArrayBufferSize=8", _

acTable , "dbo.BASE", "dbo_BASE" & Format(Date, "mmddyy")

MsgBox "Data transfer to Access is complete.", vbOKOnly, "Status"

End Sub

You'll need to Link to the table (rather than importing it), and then run an
Append query to append only the selected fields into your local table.

Alternatively, create a View on the table selecting only the desired fields
(using SQL/Server's tools) and import that view rather than the table.


John W. Vinson [MVP]

 

[BBAL20]

The DBA has put a block or lock on the table itself that won't allow the end
user to "link" to the table via an ODBC connection. That's why i need to
import the table rather than "link" the table in this instance. I was just
wondering where and how in my VB script provided above can i import specific
fields/columns rather than importing the whole table.

 

[John W. Vinson]

The DBA has put a block or lock on the table itself that won't allow the end
user to "link" to the table via an ODBC connection. That's why i need to
import the table rather than "link" the table in this instance. I was just
wondering where and how in my VB script provided above can i import specific
fields/columns rather than importing the whole table.

With that restriction, so far as I know, You Can't.

Your link *IS* an ODBC connection - I don't follow what you're saying. You
cannot link to part of a table - only to a table, or to a view.

John W. Vinson [MVP]