How to retrieve data from an SQL Server database 1

[vatawna]

I got an error trying to retrieve data into a recordset. I use the following code:

Dim ServerName As ADODB.Connection
Set ServerName = New ADODB.Connection

ServerName.ConnectionString = "Provider=SQLOLEDB.1;Data Source=any server;Initial Catalog=any database;User Id=any user id;Password=;"

ServerName.Open

Dim RS As New ADODB.Recordset
RS.Open "any_table", ServerName, adOpenStatic, adLockReadOnly, adCmdTableDirect


I got the run-time error -2147217865 (80040e37): Invalid object name.

If I replace "any_table" with "Select * from any_table", then I get the error: Table does not exist.

Does anyone know how to solve this? Thanks.

 

[MarkSweetland]

If the any_table exists in SQL Server, then "Select * from any_table" will return the contents of the table in the recordset. If the table is created under another username as the owner (and not dbo) then SQL Server will also report the object as invalid.

strSQL = "Select * from any_table"
RS.Open strSQL, ServerName, adOpenStatic, adLockReadOnly





Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[vatawna]

Mark, thanks for the prompt response. I have to check to see if the any_table is created under another username.

I have another question:

If I create a connection to an Access database which has a table linked to any_table in an SQL Server database using the following code:

ServerName.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=any path\any_database.mdb;user id=sa;password=;"

I get the run-time error "Cannot start your application. The workgroup information file is missing or opened exclusively by another user." I get the same error no matter whether the table is opened by anyone or not. Do you know how to solve this?

Thanks.

 

[MarkSweetland]

I believe you have to include the Access security file (mdw) path and filename if using a secured Access database, and the user id and password must be included.

ServerName.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=any path\any_database.mdb;" & _
"[red]Jet OLEDB:System Database=MySystem.mdw[/red];" & _
user id=sa;password=;"


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[aj20]

Hi
I am new so bear with me. I need to read data from Access and insert into SQL server. In other words, database is being migrated to SQL server, therefore I need to write a utility which can transfer data from Access to SQL server.

Thanks in advance.

 

[MarkSweetland]

aj20,

Welcome to Tek-Tips! To get the most out of the expertise available on this site, please read 'How to get the best answers:' faq222-2244

Your question should not tack on to an existing thread unless it pertains directly with the topic at hand. You need to start a new thread. Go to the Visual Basic(Microsoft) Databases Forum Home page, and at the bottom there is a 'Start a New Thread' input area. Be sure to enter a descriptive Subject line like "Migrate Access To SQL Server."

But, before you start a new thread, check out the Search facility here as well. You might find your question has been answered before...





Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach