import text file - user is not dbo

[Guest]

Hi,
We have an application in Access which is linked to SQL Server.
The code is VBA. We would like to import a text file to a table
which is owned by dbo, but the user that is running the application
is not a dbo.
When we used doCmd.TransferTextMethod we succeeded to import
data only to a table owned by the user, but didn't succeed in importing
to a table owned by dbo. any code such as dbo.<table name> didn't work.
And suggestions?

 

[Brendan Reynolds]

Have you given the user write permission on the table?

 

[Guest]

Hi Brendan,
Sorry for the late reply - I'm living in a time Zone oposite then you.
Yes. the user has write permission on the table.
The problem is that the user, let's say Jim,
So the TransferText looks for Jim.Table1
and when we wrote dbo.Table1 the Method was
looking for another table then Table1.
Is there no way to specify the table owner like
in almost any case in SQL Server?

 

[Brendan Reynolds]

When you link tables, if the SQL Server table is called, say, dbo.SomeTable,
Access will name the linked table dbo_SomeTable, because a period is an
illegal character in an Access table name. When calling the TransferText
method your code needs to use the name of the linked table, rather than the
name of the original SQL Server table. For example, if I link to
dbo.Categories in the Northwind database, Access names the linked table
'dbo_Categories', and I can import to it from a CSV file using the following
code ...

Public Sub Import()

DoCmd.TransferText acImportDelim, , "dbo_Categories", _
CurrentProject.Path & "\test.csv", True

End Sub

 

[Guest]

Thank you very much.

When you link tables, if the SQL Server table is called, say, dbo.SomeTable,
Access will name the linked table dbo_SomeTable, because a period is an
illegal character in an Access table name. When calling the TransferText
method your code needs to use the name of the linked table, rather than the
name of the original SQL Server table. For example, if I link to
dbo.Categories in the Northwind database, Access names the linked table
'dbo_Categories', and I can import to it from a CSV file using the following
code ...

Public Sub Import()

DoCmd.TransferText acImportDelim, , "dbo_Categories", _
CurrentProject.Path & "\test.csv", True

End Sub

 

[Guest]

Hi Brendan,
Access actually calls the table 'SomeTable' and not 'dbo_SomeTable'
And when we try to import into dbo.SomeTable it raises an error
that dbo_SomeTable doesn't exist.

 

[Brendan Reynolds]

The behaviour I described is the default behaviour when a SQL Server table
is linked via the UI. But there is nothing to prevent anyone from changing
the name of the linked table in Access after it has been linked, or if the
linking is done programmatically. There is no fixed relationship or
connection between the name of the linked table in Access and the name of
the source table in SQL Server. It is not the name of the linked table in
Access, but the SourceTableName property, that maintains the connection
between the linked table in Access and the source table in SQL Server.
Whatever the name of the linked table is in Access, that is the name you
need to use in your code.

 

[Guest]

I don't understand how I can link a csv file when the field has "DEALER,
LCC", and I what to delimited that column but moves LCC into the number column