[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert query from remote to local using classic vb

Posted on 2012-08-28
16
Medium Priority
?
456 Views
Last Modified: 2012-09-11
Hi
I am Using following query to insert record into local database from another local database,
and its working fine..
con.Execute ("INSERT INTO MyTable select * from [;DATABASE=" & App.Path & "\MyDatabase.mdb;PWD=abcdef].MyTable where MyField = " & MshBNo & "")

Open in new window

but now i have two database one is for local and another is from Remote SQL server.
i can retrieving records from sql server and displaying to msgrid.
now i want to insert a record from SQL server to local database using VBClassic
0
Comment
Question by:crystal_Tech
  • 7
  • 7
  • 2
16 Comments
 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38341502
Your code use an Access database, but you are asking about how to conect to a SQL server.
May be you want just to export you want your Access database to SQL server, am I right?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38341504
Is con pointing to access or sql?
0
 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38341512
Jajajajajajaja!!!!!!!!
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 1

Author Comment

by:crystal_Tech
ID: 38342165
@ LIONKING
con is pointing to access
conSrv is for SQL Server


@TI2Heaven
i am not asking how to connect sql server..?
and i dont want to export access db to sql server.?
Please read my question agian.
< i can retrieving records from sql server and displaying to msgrid.>
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38342192
Do you have access to the SQL Server (the one you say is local)?
If you do, you could create a linked server to the remote SQL Server and query using the 4 part syntax.

Something like:

SELECT * FROM LinkedServer.Database.Schema.Table WHERE myCondition

Would that work for you?
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38342360
Remote SQL Server
and local is MS Access 2003
0
 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38342410
Why don't you ask Access to link to a SQL table so you can treat sql tables as they where in access?
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38342604
@T12Heaven
<Why don't you ask Access to link to a SQL table so you can treat sql tables as they where in access?>

Please show me how to link to a Remote SQL Table
0
 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38343039
Link SQL Server tables from an Access database (.mdb)
Start Here. If you cant continue continue asking
0
 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38343174
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38343516
Thanks T12Heaven
already i did that from this link
http://www.mssqltips.com/sqlservertip/1480/configure-microsoft-access-linked-tables-with-a-sql-server-database/

now i got SQL table into my access database.
and i can access this table direct from MS Access Database

But the problem is using my application i can not retrieve records from this table.
Getting Runtime Error --  ODBC Connection to 'MYServer' failed.
Highlighted on following line

  Set rs = Con.Execute("SELECT * FROM [ODBC;DSN=MyServer;uid=myname;pwd=mypass;database=D:\MyTestDB].dbo_MyTablel_Cust")
0
 
LVL 4

Expert Comment

by:TI2Heaven
ID: 38344579
Please, before you try to connect with Visual Basic code to your Access database you need to stay in Access. Check that your link works will by designing a silly view that use the table you just linked.
If you can work with the table in Access forget about the code just send me:
Set rs = Con.Execute("SELECT * FROM [ODBC;DSN=MyServer;uid=myname;pwd=mypass;database=D:\MyTestDB].dbo_MyTablel_Cust")
As I mention you can continue using your original one:
con.Execute ("INSERT INTO MyTable select * from [;DATABASE=" & App.Path & "\MyDatabase.mdb;PWD=abcdef]. dbo_MyTablel_Cust where MyField = " & MshBNo & "")
You have not mentioned your network environment. Are you in a Domain?, Have you use user and password of your domain?.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38344817
Ok it was DSN problem , i deleted previous one and re-create new one, link is working.
from access i can open SQL Table.
One more thing,  from vb i can retrieve all records from SQL Table.

Now i am trying to insert But getting Runtime error

Field 'MyLocalTable.Field3' can not be a zero-length string


con.Execute ("INSERT INTO MyLocalTable SELECT [Field1],[Field2],[Field3],[Field4] FROM [;DATABASE=D:\MySQLTable.mdb].dbo_SqlTable_Cust WHERE Field1 = " & MshBNo & "")
0
 
LVL 4

Accepted Solution

by:
TI2Heaven earned 1600 total points
ID: 38344994
It seems that on Field3 of dbo_SqlTable_Cust table there are some null values that the table MyLocalTable cannot have.
Go to Access environment again and check the design view of table: MyLocalTable.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 38349529
yes you are right .. there is couples of fields like that..

let me fix ...
0
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 38385734
Thanks guys
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Simple Linear Regression

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question