HELP INSERT INTO error invalid argument

[edgarbenilde]

hi, can someone help me please i got error here it's saying invalid argument..


in select statement some anybody help me...

 Dim strSQL As String
            Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ph1\TSmobileData\CopyTSMobile.mdb;Persist Security Info=true;Jet OLEDB:Database password=h1lt1")
            Dim cmd As New OleDb.OleDbCommand
            cn.Open()

            strSQL = "INSERT INTO [CopyTSMobile.mdb.dbo.ttmtransaction]select * from[Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TSMobile.mdb;Persist Security Info=False;Jet OLEDB:Database Password=h1lt1].[TSMobile.mdb.dbo.ttmtransaction where sync=0]"
            cmd.CommandType = CommandType.Text
            cmd.CommandText = strSQL
            cmd.Connection = cn

            cmd.ExecuteNonQuery()
            cn.Close()

[amrita]

hi, can someone help me please i got error here it's saying invalid argument..


in select statement some anybody help me...

 Dim strSQL As String
            Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ph1\TSmobileData\CopyTSMobile.mdb;Persist Security Info=true;Jet OLEDB:Database password=h1lt1")
            Dim cmd As New OleDb.OleDbCommand
            cn.Open()

            strSQL = "INSERT INTO [CopyTSMobile.mdb.dpo.ttmtransaction]select * from[Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TSMobile.mdb;Persist Security Info=False;Jet OLEDB:Database Password=h1lt1].[TSMobile.mdb.dpo.ttmtransaction where sync=0]"
            cmd.CommandType = CommandType.Text
            cmd.CommandText = strSQL
            cmd.Connection = cn

            cmd.ExecuteNonQuery()
            cn.Close()

Why qre you using connection string in SQL stmt ?
Just use the table name.

[edgarbenilde]

the reason why i used connection string on my sql statement is that it's compose of two database... the one is the source of data where i wanted to transfer the data to another table from ther database...

anyone can help me please..?

[amrita]

What is dpo ? It should be dbo.

Also check out this link , hope it will help you.

http://p2p.wrox.com/topic.asp?TOPIC_ID=27964

[edgarbenilde]

i'm sorry armita actually it's dbo thanks..

any idea please..?

[amrita]

Have you gone through the link ? see if it can help

[edgarbenilde]

it can't be done with link because it's compose of 30 users who use the program so if i use linking process this can be done manually and it's too risky to them to update the data and those people use the system almost out of office because they are on site most of the time...

i really need the steps on problem if displayed above..because what i want is they can still use the system even if they are outside the office that is why i made them a local database then if they are in the office program will now connect to the network and those data that newly inserted in the local database would copy or inserted into the network database to make the network database updated on all data inputs of all users... they data would very different each users..

any idea or solution to solve my problem above please..?

[amrita]

Hi,

LINK meaning the URL I send you "http://p2p.wrox.com/topic.asp?TOPIC_ID=27964" in the previous post ....and not any kind of linking process...

[alex_read]

Access does appear to have it's own specific IN clause for this, but it seems this might just be for the 2007 version. I've not seen or heard of this in 2003, but you might want to research this a little more:

http://msdn.microsoft.com/en-us/library/bb208934.aspx
http://msdn.microsoft.com/en-us/library/bb177907.aspx

[alex_read]

Well I'll be damned! You were right to be persistent Edgar, you CAN do this through SQL in Access

To summarise, I created 4 databases under the root of the C:\ drive, and named them as follows:

• Access2003DBSample1.mdb
• Access2003DBSample2.mdb
• Access2007DBSample1.accdb
• Access2007DBSample2.accdb

I created a single table named Table1 in each of the 4 databases. Each table created had a single field named Field0 with a data type of Text.

Into both the 2nd Access 2003 database, and the 2nd Access 2007 database, I added 3 rows/records, entering the values 'a', 'b' and 'c' respectively.

The following code I created as a new query within the receiving 1st databases (Access 2003 and 2007) to import data from the 2nd database:

INSERT INTO Table1 SELECT * FROM Table1 IN "C:\Access2003DBSample2.mdb"

INSERT INTO Table1 SELECT * FROM Table1 IN "C:\Access2007DBSample2.accdb"

I then removed all the inserted entried, so the 1st databases (Access 2003 and 2007) contained empty tables. This time, I created and ran the following queries from the 2nd databases, this time to export data from them into the 1st databases:

INSERT INTO [C:\Access2003DBSample1.mdb].Table1 SELECT * FROM Table1

INSERT INTO [C:\Access2007DBSample1.accdb].Table1 SELECT * FROM Table1

Each of these methods I can confirm work, and are a nicer, more elegant solution to your issue/originally posted problem.